Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim r As Range, derlig&, tablo As Range, j&, dj&, a$, b$, c$
If Sh.Name Like "####" Then
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
Set r = Sh.[B6,I6,P6,W6,AD6,AK6,B30,I30,P30,W30,AD30,AK30] '1ères cellules des 12 tableaux
With Sheets("generale") 'plus d'accents ?
If .FilterMode Then .ShowAllData 'en cas de filtrage
derlig = .Cells(.Rows.Count, 1).End(xlUp).Row
If derlig > 1 Then
Set tablo = .Range("A2:G" & derlig)
tablo.Sort tablo(1), xlAscending, Header:=xlNo 'tri sur les dates
Set tablo = tablo.Resize(Application.Count(tablo.Columns(1))) 'en cas de lignes vides
'tablo.Name = "TB" 'si l'on voulait utiliser ce nom ailleurs
End If
End With
For Each r In r
Set r = r.Resize(20, 5) 'le tableau à remplir
If derlig < 2 Then r = "": GoTo 1
j = DateSerial(Year(r(-1, 0)), Month(r(-1, 0)), 1) '1er jour du mois, au cas où...
dj = DateSerial(Year(j), Month(j) + 1, 0) 'dernier jour du mois
If dj < tablo(1) Or j > tablo(tablo.Rows.Count, 1) Then r = "": GoTo 1
If j < tablo(1) Then j = 1 Else j = Application.Match(j, tablo.Columns(1))
tablo.Rows(j).Resize(31).Name = "T" 'nom défini T sur 31 jours
a = r(-1, 0).Address '$A$4 sur le 1er tableau
b = r(1, 0).Address(0, 1) '$A6 sur le 1er tableau
c = b & ":" & r(1).Address(0, 0) '$A6:B6 sur le 1er tableau
r = "=SUMPRODUCT((YEAR(INDEX(T,,1))=YEAR(" & a & "))*(MONTH(INDEX(T,,1))=MONTH(" & a & "))*(INDEX(T,,COLUMNS(" & c & "))=" & b & "))"
r = r.Value 'supprime les formules
1 Next
Application.EnableEvents = True 'réactive les évènements
End If
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Workbook_SheetActivate Sh 'appelle la macro
End Sub