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 124 125 126
| Private Sub Afficher_Click()
Dim LastLigS1 As Long, LastLigS2 As Long, NewLigC1 As Long, NewLigC2 As Long, NewLigC3 As Long, i As Long
Dim Année As Integer, Avct As String
Dim c As Range, d As Range, e As Range
Application.ScreenUpdating = False
'Ouvrir le fichier "Archives observations"
Set Wbk = Workbooks.Open("E:\Rapports d'audits EHS\Archives observations.xlsm")
Annee = CbB_Archivage.Value
Avct = "100%"
Set WsC1 = Wbk.Worksheets("Archive des données") '1ère feuille cible
Set WsC2 = Wbk.Worksheets("Archive des Actions Soldées") '2ème feuille cible
Set WsC3 = Wbk.Worksheets("Liste") '3ème feuille cible
Set WsS1 = Workbooks("HRQF898-01").Worksheets("Recueil données") '1ère feuille source
Set WsS2 = Workbooks("HRQF898-01").Worksheets("Rapport") '2ème feuille source
Set WsS3 = Workbooks("HRQF898-01").Worksheets("Suivi mensuel") '3ème feuille source
'Extraction du nombre d'observations ouvertes vers fichier archivage
NewLigC3 = WsC3.Cells(WsC3.Rows.Count, 6).End(xlUp).Row + 1
With WsS3
'Copie des lignes à sélectionner dans cellules de destinations
Set e = .Range("Y47:Z47")
If Not e Is Nothing Then
'Copier
e.Copy
'Collage spécial
WsC3.Cells(NewLigC3, 6).PasteSpecial Paste:=xlPasteValues
Set e = Nothing
NewLigC3 = NewLigC3 + 1
End If
End With
NewLigC3 = 0
Set WsC3 = Nothing
'Extraction des lignes = à l'année passée, de "Recueil données" vers fichier archivage
If Annee = CbB_Archivage.Value Then
NewLigC1 = WsC1.Cells(WsC1.Rows.Count, 1).End(xlUp).Row + 1
With WsS1
'Copie des lignes à sélectionner dans cellules de destinations
LastLigS1 = .Cells(.Rows.Count, 1).End(xlUp).Row
If LastLigS1 >= 171 Then
For i = 171 To LastLigS1
Set c = .Range("A" & i & ":AV" & i).Find(Annee, LookIn:=xlValues, Lookat:=xlWhole)
If Not c Is Nothing Then
'Copier
c.EntireRow.Copy
'Collage spécial
WsC1.Cells(NewLigC1, 1).PasteSpecial Paste:=xlPasteValues
Set c = Nothing
NewLigC1 = NewLigC1 + 1
End If
Next i
For i = 171 To LastLigS1
Set c = .Range("A" & i & ":AV" & i).Find(Annee, LookIn:=xlValues, Lookat:=xlWhole)
If Not c Is Nothing Then
'Suppression
c.EntireRow.Delete
Set c = Nothing
End If
Next i
End If
End With
NewLigC1 = 0
End If
Set WsC1 = Nothing
'Extraction des lignes = à l'année passée, de "Rapport" vers fichier archivage
If Annee = CbB_Archivage.Value Then
NewLigC2 = WsC2.Cells(WsC2.Rows.Count, 1).End(xlUp).Row + 1
With WsS2
'Démasquage de colonnes
WsS2.Columns("A:F").EntireColumn.Hidden = False
WsS2.Columns("P:AA").EntireColumn.Hidden = False
'Copie des lignes à sélectionner dans cellules de destinations
LastLigS2 = .Cells(.Rows.Count, 1).End(xlUp).Row
'Désactivation de tous les filtres "gpe de section" et "avancement"
.Range("$A$26:$Q$" & LastLigS2).AutoFilter Field:=4 '"gpe de section"
.Range("$A$26:$Q$" & LastLigS2).AutoFilter Field:=6 '"Opé/poste"
.Range("$A$26:$Q$" & LastLigS2).AutoFilter Field:=15 '"avancement"
'Activation des filtres de la colonne "Avct"
.Range("$A$26:$Q$" & LastLigS2).AutoFilter Field:=15, Criteria1:="100%"
If LastLigS2 >= 27 Then
For i = 27 To LastLigS2
Set d = .Range("A" & i & ":Q" & i).Find(Annee, LookIn:=xlValues, Lookat:=xlWhole)
If Not d Is Nothing Then
'Copier
d.EntireRow.Copy
'Collage spécial
WsC2.Cells(NewLigC2, 1).PasteSpecial Paste:=xlPasteValues
Set d = Nothing
NewLigC2 = NewLigC2 + 1
End If
Next i
For i = 27 To LastLigS2
Set d = .Range("A" & i & ":Q" & i).Find(Annee, LookIn:=xlValues, Lookat:=xlWhole)
If Not d Is Nothing Then
'Suppression
d.EntireRow.Delete
Set d = Nothing
End If
Next i
End If
.Range("$A$26:$Q$" & LastLigS2).AutoFilter Field:=15, Criteria1:=Array("0%", "25%", "50%", "75%", "="), Operator:=xlFilterValues
WsS2.Columns("A:F").EntireColumn.Hidden = True
WsS2.Columns("P:AA").EntireColumn.Hidden = True
End With
NewLigC2 = 0
End If
Set WsC2 = Nothing
Application.ScreenUpdating = True
With Workbooks("Archives observations")
.Save
.Close
End With
Unload Me
End Sub |
Partager