tri automatique

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [B1]) Is Nothing Then Exit Sub
Range("A2:Z" & Rows.Count).ClearContents 'RAZ
If [B1] = "" Then Exit Sub
Dim lig&, cel As Range, col%
Application.ScreenUpdating = False
With Feuil1 'CodeName
  .AutoFilterMode = False
  With .Range("AH7", .Cells(.Rows.Count, "AH").End(xlUp))
    .AutoFilter 1, IIf([B1] = "Tous", "*", [B1]) 'filtre automatique
    .Offset(, -2).Resize(, 4).SpecialCells(xlCellTypeVisible).Copy [A2]
    .Offset(, -4).Resize(, 2).SpecialCells(xlCellTypeVisible).Copy [C2]
    lig = 2
    For Each cel In .SpecialCells(xlCellTypeVisible)
      For col = 3 To 24
        If LCase(.Parent.Cells(cel.Row, col)) = "o" Then _
          Cells(lig, col + 2) = .Parent.Cells(5, col)
      Next
      lig = lig + 1
    Next
  End With
  .AutoFilterMode = False
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [D1]) Is Nothing Then Exit Sub
If [D1] = "" Then Exit Sub
Dim n&
With Feuil1 'CodeName
  .AutoFilterMode = False
  With .Range("BD7", .Cells(.Rows.Count, "BD").End(xlUp))
    .AutoFilter 1, [D1] 'filtre automatique
    .Offset(, -26).Resize(, 26).SpecialCells(xlCellTypeVisible).Copy [A2]
    n = .SpecialCells(xlCellTypeVisible).Count
  End With
  .AutoFilterMode = False
End With
'---tri du filtre---
[AA2].Resize(n).FormulaR1C1 = "=MATCH(""?*"",RC5:RC26,0)"
[A2:AA2].Resize(n).Sort [AA2], xlAscending, Header:=xlYes
[AA:AA].ClearContents
Range([A2:Z2].Offset(n), Cells(Rows.Count, 1)).ClearContents
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [D1]) Is Nothing Then Exit Sub
If [D1] <> "" Then
  Dim n&
  With Feuil1 'CodeName
    Application.ScreenUpdating = False
    .AutoFilterMode = False
    With .Range("BD7", .Cells(.Rows.Count, "BD").End(xlUp))
      .AutoFilter 1, IIf([D1] = "Tous", "*", [D1]) 'filtre automatique
      .Offset(, -26).Resize(, 26).SpecialCells(xlCellTypeVisible).Copy
      [A2].PasteSpecial xlPasteValues
      n = .SpecialCells(xlCellTypeVisible).Count
    End With
    .AutoFilterMode = False
  End With
  '---tri du filtre---
  [AA2].Resize(n).FormulaR1C1 = "=MATCH(""?*"",RC5:RC26,0)"
  [A2:AA2].Resize(n).Sort [AA2], xlAscending, Header:=xlYes
  [AA:AA].ClearContents
  Target.Select
End If
Range([A2:Z2].Offset(n), Cells(Rows.Count, 1)).ClearContents
End Sub