Private Sub Worksheet_Activate()
Dim wb As Workbook
Application.ScreenUpdating = False
Rows("2:" & Rows.Count).Delete 'RAZ
Sheets("Pilon").Visible = xlSheetVisible 'au cas où...
Sheets("Pilon").Copy 'document auxiliaire
Set wb = ActiveWorkbook
With wb.Sheets(1)
wb.Names.Add "Crit", IIf(.[B3] = "", "*", Split(.[B3], ", ")) 'nom défini (matriciel)
wb.Names.Add "Date1", CDbl(CDate(Mid(.[B4], 7, 10))) 'nom défini
wb.Names.Add "Date2", CDbl(CDate(Right(.[B4], 10))) 'nom défini
.Rows(11).Clear 'sécurité, pour bien isoler le tableau
With .[A12].CurrentRegion.Resize(, 11)
.Columns(2).UnMerge 'défusionne
If Application.CountBlank(.Columns(2)) Then .Columns(2).SpecialCells(xlCellTypeBlanks) = "=R[-1]C" 'remplit les cellules vides
.Columns(2) = .Columns(2).Value 'supprime les formules
.Cells(2, 13) = "=SUMPRODUCT(COUNTIF(A13:K13,""*""&Crit&""*""))*(I13>=Date1)*(I13<=Date2)" 'formule de la zone de critère
.AdvancedFilter xlFilterCopy, .Cells(1, 13).Resize(2), Me.[A1:K1] 'filtre avancé
End With
End With
wb.Close False 'fermeture du document auxiliaire
End Sub