Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
[2:65536].Delete 'RAZ
Filtre 5, 1, [A2]
Filtre 6, "X", [G2]
Application.ScreenUpdating = True
End Sub
Sub Filtre(col As Byte, crit, dest As Range)
'Feuil1 est le CodeName
Dim plage As Range, h As Long
Set plage = Feuil1.Range("A3", Feuil1.[A65536].End(xlUp)).Resize(, col)
plage.AutoFilter
plage.AutoFilter col, crit
With plage.SpecialCells(xlCellTypeVisible)
.Copy dest
h = .Count / col
End With
plage.AutoFilter
dest(1, col).Resize(h) = dest(1, col).Resize(h).Value
If col = 6 Then col = col - 1: dest(1, col).Resize(h).Delete xlToLeft
If h > 1 Then
dest(1, col).FormulaR1C1 = "=COUNTIF(R[1]C:R[" & h - 1 & "]C,""" & crit & """)"
With dest.Offset(h).Resize(, 4)
.FormulaR1C1 = "=SUMPRODUCT(--R[" & 1 - h & "]C:R[-1]C)"
.Cells(1) = "Totaux"
.HorizontalAlignment = xlCenter
.Borders.Weight = xlThin
End With
End If
End Sub