Private Sub Worksheet_Activate()
If ListObjects.Count = 0 Then Exit Sub
Dim br As Range, d As Object, w As Worksheet, i&
Dim a, ncol%, tablo(), lig As Variant, j%
Set br = ListObjects(1).DataBodyRange
Application.ScreenUpdating = False
br.Offset(1).EntireRow.Delete 'RAZ
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
'---liste des élèves sns doublon---
For Each w In Worksheets
If LCase(w.Name) Like "#*trim*" Then
With w.ListObjects(1).DataBodyRange
For i = 2 To .Rows.Count
d(.Cells(i, 2).Value) = ""
Next i
End With
End If
Next w
If d.Count = 0 Then Exit Sub
'---création du tableau---
a = d.keys
ncol = br.Columns.Count - 4 '-1 colonne au début et -3 à la fin
ReDim tablo(1 To UBound(a) + 1, 1 To ncol)
For i = 1 To UBound(tablo)
tablo(i, 1) = a(i - 1)
For Each w In Worksheets
If LCase(w.Name) Like "#*trim*" Then
With w.ListObjects(1).DataBodyRange
lig = Application.Match(a(i - 1), .Columns(2), 0)
If IsNumeric(lig) Then
For j = 2 To ncol
If IsNumeric(CStr(.Cells(lig, j + 1))) Then _
tablo(i, j) = tablo(i, j) + .Cells(lig, j + 1)
Next j
End If
End With
End If
Next w
Next i
'---restitution---
[MOYENNES].EntireRow.Resize(UBound(tablo)).Insert
br(2, 2).Resize(UBound(tablo), ncol) = tablo
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If ListObjects.Count = 0 Then Exit Sub
'---décale les moyennes---
With ListObjects(1).DataBodyRange
If .Rows(.Rows.Count).Row + 1 = [MOYENNES].Row Then
[MOYENNES].EntireRow.Insert
[MOYENNES].EntireRow(0).ClearFormats 'efface la ligne précédente
End If
End With
End Sub