Private Sub Worksheet_Activate()
Worksheet_Change [A1] 'lance la macro pour mettre à jour le tableau
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim clien$, mois As Byte, an%, tablo, resu(), d As Object, i&, x$, n&, lig&
client = [B2]
mois = Month([B3]): an = Year([B3])
tablo = Sheets("MOUV_SORTIES").[A1].CurrentRegion.Resize(, 6) 'matrice, plus rapide
ReDim resu(1 To UBound(tablo), 1 To 4)
Set d = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(tablo)
If tablo(i, 2) = client And Month(tablo(i, 6)) = mois And Year(tablo(i, 6)) = an Then
x = tablo(i, 3) & Chr(1) & tablo(i, 5) 'en cas de prix différents pour un même produit
If Not d.exists(x) Then
n = n + 1
d(x) = n 'mémorise la ligne
resu(n, 1) = tablo(i, 1)
resu(n, 2) = tablo(i, 3)
resu(n, 4) = tablo(i, 5)
End If
lig = d(x)
resu(lig, 3) = resu(lig, 3) + tablo(i, 4)
End If
Next
'---restitution---
Application.EnableEvents = False 'désactive les évènements
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [E3]
If n Then
.Resize(n, 4) = resu
.Resize(n, 4).Borders.Weight = xlThin 'bordures
End If
.Offset(n).Resize(Rows.Count - n - .Row + 1, 4).Delete xlUp 'RAZ en dessous
End With
Application.EnableEvents = True 'réactive les évènements
End Sub