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
| Sub CopieFiltre()
Dim ClSource As Workbook
Dim ClCible As Workbook
Dim Plage As Range
Dim Critere As String
'utilisation de variables pour éviter la confusion
Set ClSource = ThisWorkbook
Set ClCible = Workbooks.Add
'adapter le critère
Critere = "Client1"
'défini la plage sur toute la feuille
Set Plage = DefPlage(ClSource.Worksheets("Feuil1"))
With ClSource.Worksheets("Feuil1")
'filtrage sur la colonne 1 de la plage (ici A)
Plage.AutoFilter 1, "=" & Critere
'copie du résultat dans la feuille "Feuil1" du nouveau classeur
'attention, la copie embarque les entêtes
.AutoFilter.Range.EntireRow.Copy ClCible.Worksheets("Feuil1").Cells(1, 1)
'suppression du filtrage
Plage.AutoFilter
With ActiveWorkbook
.SaveAs Filename:="C:\Users\jerome.hadji-yoannou\Desktop\Extract\" & Critere
.Close
End With
End With
End Sub
Function DefPlage(Fe As Worksheet, Optional L As Long = 1, Optional C As Long = 1) As Range
On Error GoTo Fin
With Fe
Set DefPlage = .Range(.Cells(L, C), _
.Cells(.Cells.Find("*", .[A1], -4123, , _
1, 2).Row, .Cells.Find("*", .[A1], -4123, , _
2, 2).Column))
End With
Exit Function |
Partager