Private Sub Worksheet_Activate()
Dim tablo, ncol%, d As Object, i&, j%, dat As Date, mois, item, x$
'---liste feuille Synthese---
With Feuil55 'CodeName de la feuille Synthese
If .FilterMode Then .ShowAllData 'si la feuille est filtrée
tablo = .Range("C40:X" & .Range("C" & .Rows.Count).End(xlUp).Row) 'matrice, plus rapide
End With
ncol = UBound(tablo, 2)
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(tablo)
For j = 12 To ncol
If IsDate(tablo(i, j)) Then
dat = DateSerial(Year(tablo(i, j)), Month(tablo(i, j)), 1)
d(dat & tablo(i, 1)) = tablo(i, j) 'mémorise la date
End If
Next j, i
'--tableau feuille compilation---
If FilterMode Then ShowAllData 'si la feuille est filtrée
With Range("G7:BE" & Range("C" & Rows.Count).End(xlUp).Row)
tablo = .Value 'matrice, plus rapide
ncol = UBound(tablo, 2)
mois = .Rows(-1) '2 lignes au-dessus
item = .Columns(-3) '4 colonnes à gauche
For i = 1 To UBound(tablo)
For j = 1 To ncol
If IsDate(mois(1, j)) Then tablo(i, j) = d(CDate(mois(1, j)) & item(i, 1))
Next j, i
.Value = tablo 'restitution
End With
End Sub