Private Sub Worksheet_Activate()
Dim d As Object, w As Worksheet, sem, t, i&, nom$, n&, rest()
Set d = CreateObject("Scripting.Dictionary")
For Each w In Worksheets
If LCase(w.Name) Like "s#*" Then
sem = Val(Mid(w.Name, 2))
t = w.[A1].CurrentRegion.Resize(, 3)
For i = 2 To UBound(t)
nom = t(i, 1)
If Not d.exists(nom) Then
n = n + 1
d(nom) = n 'repérage
ReDim Preserve rest(1 To 6, 1 To n)
'ligne 1 nom
'ligne 2 somme AAA
'ligne 3 dernier BBB
'ligne 4 moyenne
'ligne 5 nombre d'occurences du nom
'ligne 6 numéro de la dernière semaine
rest(1, n) = nom
End If
rest(2, d(nom)) = rest(2, d(nom)) + t(i, 2) 'somme
If sem > rest(6, d(nom)) Then rest(6, d(nom)) = sem: rest(3, d(nom)) = t(i, 3)
rest(5, d(nom)) = rest(5, d(nom)) + 1 'nombe d'occurences du nom
rest(4, d(nom)) = rest(2, d(nom)) / rest(5, d(nom)) 'moyenne
Next i
End If
Next w
If n Then [A2].Resize(n, 4) = Application.Transpose(rest)
Rows(n + 2 & ":" & Rows.Count).Delete
End Sub