Private Sub Worksheet_Activate()
Dim t(1 To 20, 1 To 5), w As Worksheet, nomtab$, total As Range, i, j
For Each w In Worksheets
If w.Name Like ("####") Then
nomtab = "TOTAL " & Right(w.Name, 2)
Set total = w.Cells.Find(nomtab, , xlValues, xlWhole)
If total Is Nothing Then
MsgBox "Tableau '" & nomtab & "' non trouvé dans la feuille '" & w.Name & "'." _
& vbLf & "Cette feuille n'est donc pas étudiée.", 48
Else
For i = 1 To 20
For j = 1 To 5
t(i, j) = t(i, j) + Val(total(i + 2, j + 1))
Next j, i
End If
End If
Next
[D7:H26] = t
End Sub