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
| Sub FilterAndCopy()
Dim opr, Dats, DstSheet, rng, lastRow
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set Dats = Sheets("Données")
lastRow = Dats.Cells(Dats.Rows.Count, 1).End(xlUp).Row
Set rng = Dats.Range("A1:G" & lastRow)
For Each opr In Dats.Range("Opérateurs")
On Error Resume Next
Sheets(CStr(opr)).Delete 'supprimer la feuille
If Err.Number <> 0 Then: Err.Clear ' la feuille n'existe pas
Set DstSheet = Sheets.Add(After:=Sheets(Sheets.Count))
DstSheet.Name = CStr(opr)
rng.Rows(2).AutoFilter Field:=6, Criteria1:=opr
rng.SpecialCells(xlCellTypeVisible).Copy
With DstSheet.Range("A1")
.PasteSpecial Paste:=xlPasteColumnWidths
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteValues
End With
DstSheet.Range("aA2").Select
Next
rng.AutoFilter
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub |
Partager