1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123
| Private Sub Afficher_Click()
Dim LastLig As Long, NewLig As Long, i As Long, DerLig As Long
Dim Année As Integer, Avct As String
Dim c As Range, d As Range, e As Range
'Ouvrir le fichier "Archives observations"
Application.ScreenUpdating = False
Set Wbk = Workbooks.Open("E:\AFS\Audit EHS\Vba\Archives observations.xlsm")
Annee = CbB_Archivage.Value
Avct = "100%"
Set Ws1 = Wbk.Worksheets("Archive des données")
'Extraction des lignes = à l'année passée, de "Recueil données" vers fichier archivage
If Annee = CbB_Archivage.Value Then
NewLig = Ws1.Cells(Ws1.Rows.Count, 1).End(xlUp).Row + 1
With Workbooks("HRQF898-01").Worksheets("Recueil données")
'Copie des lignes à sélectionner dans cellules de destinations
LastLig = .Cells(.Rows.Count, 1).End(xlUp).Row
If LastLig >= 171 Then
For i = 171 To LastLig
Set c = .Range("A" & i & ":AV" & i).Find(Annee, LookIn:=xlValues, Lookat:=xlWhole)
If Not c Is Nothing Then
c.EntireRow.Copy
Ws1.Range("A" & NewLig).PasteSpecial Paste:=xlPasteValues
c.EntireRow.Delete
Set c = Nothing
NewLig = NewLig + 1
End If
Next i
End If
End With
End If
Set Ws1 = Nothing
Set Ws2 = Wbk.Worksheets("Archive des Actions Soldées")
NewLig = 1
'Extraction des lignes = à l'année passée et = à 100%, de "Rapport" vers fichier archivage
If Année = CbB_Archivage.Value Then
NewLig = Ws2.Cells(Ws2.Rows.Count, 1).End(xlUp).Row + 1
With ThisWorkbook.Worksheets("Rapport")
DerLig = Range("F" & .Rows.Count).End(xlUp).Row
'Sélection de toutes les données dans les filtres "gpe de section" et "avancement"
.Range("$A$26:$Q$" & DerLig).AutoFilter Field:=4 '"gpe de section"
.Range("$A$26:$Q$" & DerLig).AutoFilter Field:=6 '"Opé / Poste"
.Range("$A$26:$Q$" & DerLig).AutoFilter Field:=15 '"avancement"
'Tri par ordre croissant de la colonne "Avct"
.AutoFilter.Sort.SortFields.Clear
.AutoFilter.Sort.SortFields.Add Key:= _
Range("O26" & DerLig), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With .AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Copie des lignes à sélectionner dans cellules de destinations
LastLig = .Cells(.Rows.Count, "A").End(xlUp).Row
If LastLig >= 27 Then
For i = 27 To LastLig
Set d = .Range("A" & i & ":Q" & i).Find(Avct, LookIn:=xlValues, Lookat:=xlWhole)
If Not d Is Nothing Then
d.EntireRow.Copy
Ws2.Range("A" & NewLig).PasteSpecial Paste:=xlPasteValues
d.EntireRow.Delete
Set d = Nothing
NewLig = NewLig + 1
End If
Next i
End If
End With
End If
Set Ws2 = Nothing
NewLig = 1
'Copie du contenu de la cellule "Y47:Z47" du "Suivi mensuel" vers fichier archivage
NewLig = Range("F" & Rows.Count).End(xlUp).Row + 1
Workbooks("Archives observations").Worksheets("Liste").Range("F" & NewLig).Value = Workbooks("HRQF898-01").Worksheets("Rapport").Range("Y47:Z47")
NewLig = NewLig + 1
'Rafraichissement des filtres "Rapport"
With Workbooks("HRQF898-01").Worksheets("Rapport")
DerLig = Range("E" & .Rows.Count).End(xlUp).Row
.AutoFilter.Sort.SortFields.Clear
.AutoFilter.Sort.SortFields.Add Key:= _
Range("A26" & DerLig), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With .AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
'Rafraichissement des filtres "Recueil données"
With Workbooks("HRQF898-01").Worksheets("Recueil données")
DerLig = Range("A" & .Rows.Count).End(xlUp).Row
.AutoFilter.Sort.SortFields.Clear
.AutoFilter.Sort.SortFields.Add _
Key:=Range("A1" & DerLig), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With .AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Application.ScreenUpdating = True
'With Workbooks("Archives observations")
' .Save
' .Close
'End With
Unload Me
End Sub |
Partager