Private Sub CommandButton4_Click()
Dim Ws As Worksheet
Dim derlig&, plage As Range, i&, t, d As Object
Dim k&, fl()
fl = Array("S0101", "S0201", "S0301", "S0401", "S0501") 'Liste des onglets à traiter.
For k = 0 To UBound(fl)
On Error GoTo E 'Pour le cas où l'onglet n'existe pas...
Worksheets(fl(k)).Activate
On Error GoTo 0
derlig = [B3].End(xlDown).Row
If derlig <> Rows.Count Then
Application.ScreenUpdating = False
Set plage = Range("A3:I" & derlig)
'---tableau préparatoire trié---
Range("A" & derlig + 1 & ":I" & Rows.Count).Delete xlUp 'RAZ
plage.Copy Cells(derlig + 1, 1)
Set plage = plage.Offset(plage.Rows.Count)
For i = 2 To plage.Rows.Count 'pour la 2ème clé de tri
t = Trim(plage.Cells(i, 8))
If t = "rouge" Then plage.Cells(i, 9) = 1
If t = "orange" Then plage.Cells(i, 9) = 2
If t = "jaune" Then plage.Cells(i, 9) = 3
If t = "" Then plage.Cells(i, 9) = 4
Next
plage.Sort [B1], xlAscending, [I1], , xlAscending, [G1], xlAscending, xlYes
plage.Columns(9).ClearContents
'---liste des titres des tableaux---
Set d = CreateObject("Scripting.Dictionary")
For i = 2 To plage.Rows.Count
d(plage.Cells(i, 2).Value) = plage.Cells(i, 2).Value
Next
'---création des tableaux---
ActiveSheet.AutoFilterMode = False
For Each t In d.keys
derlig = Cells(Rows.Count, 2).End(xlUp).Row
Cells(derlig + 3, 2) = t
Cells(derlig + 3, 2).Borders.LineStyle = 1 'bordures
plage.AutoFilter 2, t 'filtre automatique
plage.SpecialCells(xlCellTypeVisible).Copy Cells(derlig + 5, 1)
plage.AutoFilter
Next
ActiveSheet.AutoFilterMode = False
plage.Delete xlUp
End If
S: Next k
Exit Sub
E: Resume S
End Sub