Private Sub Worksheet_Activate()
Dim dico As Object, w As Worksheet, c As Range, occurence As String
Set dico = CreateObject("Scripting.Dictionary")
For Each w In Worksheets
If w.Name <> "Synthèse" Then
For Each c In w.Range("A1", w.UsedRange).Columns(9).Cells
occurence = Trim(CStr(c))
If occurence <> "" Then dico(occurence) = dico(occurence) + 1
Next c
End If
Next w
Application.ScreenUpdating = False
With Sheets("Synthèse")
If .FilterMode Then .ShowAllData 'si la feuille est filtrée
.[B1].CurrentRegion.Offset(1).ClearContents 'RAZ sous les titres
If dico.Count Then
.[B2].Resize(dico.Count) = Application.Transpose(dico.keys)
.[C2].Resize(dico.Count) = Application.Transpose(dico.items)
.[B2].Resize(dico.Count, 2).Sort .[C2], xlDescending, Header:=xlNo 'tri décroissant
End If
With .UsedRange: End With 'actualise la barre de défilement verticale
End With
End Sub