Private Sub Worksheet_Activate()
Dim a(), w As Worksheet, n%
ReDim a(1 To Worksheets.Count, 1 To 11) 'tableau, plus rapide
Application.ScreenUpdating = False
If FilterMode Then ShowAllData 'si la feuille est filtrée
Columns(1).ClearFormats 'RAZ
Range("B2:J" & Rows.Count).ClearContents 'RAZ
For Each w In Worksheets
If w.Name Like "#*-##" Then
n = n + 1
Cells(n + 1, 1).Interior.ColorIndex = w.Tab.ColorIndex 'couleur
a(n, 2) = w.Cells(2, 1)
a(n, 3) = w.Cells(2, 6)
a(n, 4) = w.Cells(2, 2)
If Application.Count(w.Columns(2)) Then a(n, 5) = Application.Lookup(9 ^ 9, w.Columns(2))
a(n, 6) = w.Cells(2, 4)
a(n, 7) = w.Cells(2, 5)
a(n, 8) = w.Cells(2, 7)
a(n, 9) = w.Cells(2, 8)
a(n, 10) = w.Cells(2, 9)
If Application.Count(w.Columns(11)) Then a(n, 11) = Application.Lookup(9 ^ 9, w.Columns(11))
End If
Next
If n Then [A2].Resize(n, 11) = a 'restitution du tableau
Columns.AutoFit 'ajustement largeur
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub