Testé sur office 2002 et 2007
Sub filtre()
Sheets("Feuil1").Select
If ActiveSheet.AutoFilterMode Then
ActiveSheet.Cells.Select
Selection.AutoFilter
End If
'tri
dernligne = ActiveSheet.Range("D60000").End(xlUp).Offset(0, 0).Row 'je ne me rappelle plus du nbre de ligne max ds excel 2000
Range("A5").CurrentRegion.Select
Selection.Sort Key1:=Range("D6:D" & dernligne), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'filtre
Application.GoTo Reference:="Liste" 'zone de nom définie dans la feuille colonne D
For Each S In Selection
If S <> "" Then
Sheets("Feuil1").Select
Range("A5").Select
Selection.CurrentRegion.Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:=S
For Each fich In ThisWorkbook.Worksheets
fname = fich.Name
If fname = S Then
GoTo 1
Else
End If
Sheets("Feuil1").Activate
Next
Selection.CurrentRegion.Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
ActiveSheet.Name = S
1
Else
End If
Next
Sheets("Feuil1").Select
If ActiveSheet.AutoFilterMode Then
ActiveSheet.Cells.Select
Selection.AutoFilter
End If
End Sub