Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [B2]) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
With ListObjects(1).Range
.AutoFilter
.AutoFilter 4, [B2] 'filtre automatique
Intersect(.Offset(1), [I:K]).Copy 'copie les colonnes I J K sans les titres
End With
With Workbooks.Add.Sheets(1) 'document auxiliaire vierge
.[A1].PasteSpecial xlPasteValues 'collage spécial-valeurs
With .UsedRange
.Columns(3).Copy .Columns(4)
If Application.CountA(.Columns(4)) Then .Columns(4).Sort .Cells(1, 4), xlAscending, Header:=xlNo 'tri
.Columns(4).RemoveDuplicates 1, Header:=xlNo 'élimine les doublons
.Columns(5).Resize(8) = "=SUMIF(" & .Columns(3).Address & ",D1," & .Columns(1).Address & ")"
.Columns(6).Resize(8) = "=SUMIF(" & .Columns(3).Address & ",D1," & .Columns(2).Address & ")"
[L4:N11] = .Columns(4).Resize(8, 3).Value 'restitution
End With
.Parent.Close False 'ferme le document auxiliaire
End With
End Sub