XL 2016 Erreur sur liste déroulante dynamique

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$C$24" Then
    With Sheets("Carnet")
        .[A22:AL1500].AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.[BA1], Unique:=True
        With .Columns("BA")
            .Sort .Cells, xlAscending, Header:=xlYes 'tri
            Target.Validation.Delete
            With .Parent.Range(.Cells(2), .Cells(.Rows.Count).End(xlUp))
                If .Row > 1 Then
                    .Cells.Name = "M" 'plage nommée
                    Target.Validation.Add xlValidateList, Formula1:="=M" 'crée la liste de validation
                End If
            End With
        End With
    End With
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(ActiveCell, [B24:J24]) Is Nothing Then Exit Sub
Dim col As Variant, t, d As Object, i&, n&, x$
With Sheets("Carnet")
    col = Application.Match(ActiveCell(0, 1), .Rows(22), 0)
    If IsError(col) Then GoTo 1
    If .FilterMode Then .ShowAllData 'si la feuille est filtrée
    With .Range("A23:A" & .Range("B" & .Rows.Count).End(xlUp).Row)
        If .Row < 23 Then GoTo 1
        t = .Columns(col).Resize(.Rows.Count + 1).Value2 'tableau VBA, plus rapide, au moins 2 éléments
    End With
End With
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(t) - 1
    x = Trim(t(i, 1))
    If x <> "" Then d(x) = ""
Next
n = d.Count
1 ActiveCell.Validation.Delete
With [AM2] 'cellule à adapter éventuellement
    .EntireColumn.NumberFormat = "dd/mm/yyyy" 'format date
    If n Then
        .Resize(n) = Application.Transpose(d.keys)  'avec Transpose maximum 65536 lignes
        .Resize(n).Sort .Cells, xlAscending, Header:=xlNo
        .Resize(n).Name = "Liste" 'plage nommée
        ActiveCell.Validation.Add xlValidateList, Formula1:="=Liste" 'liste de validation
    End If
    .Offset(n).Resize(Rows.Count - n - .Row + 1).ClearContents 'RAZ en dessous
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [B24:J24]) Is Nothing Then Exit Sub
Dim t, j%, txt$, col, ub%, ncol%, i&, x$, n&
t = [B24:J24].Value2 'date au format nombre
For j = 1 To UBound(t, 2)
    txt = txt & Chr(1) & IIf(t(1, j) = "", "*", t(1, j)) 'utilise le caractère générique *
Next
col = Array(2, 4, 12, 13, 16, 29, 30, 31, 32) 'colonnes à filtrer, à adapter
ub = UBound(col)
With Sheets("Carnet")
    If .FilterMode Then .ShowAllData 'si la feuille est filtrée
    With .Range("A23:AK" & .Range("B" & .Rows.Count).End(xlUp).Row)
        If .Row < 23 Then GoTo 1
        t = .Value2 'tableau VBA, plus rapide
        ncol = UBound(t, 2)
    End With
End With
For i = 1 To UBound(t)
    x = ""
    For j = 0 To ub
        x = x & Chr(1) & Trim(t(i, col(j)))
    Next j
    If x Like txt Then
        n = n + 1
        For j = 1 To ncol
            t(n, j) = t(i, j)
        Next j
    End If
Next i
1 With [A29] 'cellule à adapter éventuellement
    If n Then .Resize(n, ncol) = t
    .Offset(n).Resize(Rows.Count - n - .Row + 1, ncol).ClearContents 'RAZ en dessous
End With
Columns.AutoFit 'ajustement largeur
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub

Private Sub Worksheet_Change(ByVal Target As Range) 'by job75
If Intersect(Target, [B24:J24]) Is Nothing Then Exit Sub
Dim t, j%, txt$, col, ub%, ncol%, i&, x$, n&
t = [B24:J24]
For j = 1 To UBound(t, 2)
    If IsDate(t(1, j)) Then t(1, j) = CDbl(CDate(t(1, j)))
    txt = txt & Chr(1) & IIf(t(1, j) = "", "*", t(1, j)) 'utilise le caractère générique *
Next
col = Array(2, 4, 12, 13, 16, 29, 30, 31, 32) 'colonnes à filtrer, à adapter
ub = UBound(col)
With Sheets("CarnetDeTrades")
    If .FilterMode Then .ShowAllData 'si la feuille est filtrée
    With .Range("A23:AK" & .Range("A" & .Rows.Count).End(xlUp).Row)
        If .Row < 23 Then GoTo 1
        t = .Value2 'tableau VBA, plus rapide
        ncol = UBound(t, 2)
    End With
End With
For i = 1 To UBound(t)
    x = ""
    For j = 0 To ub
        x = x & Chr(1) & IIf(Trim(t(i, col(j))) = "", "*", Trim(t(i, col(j))))
    Next j
    If x Like txt Then
        n = n + 1
        For j = 1 To ncol
            t(n, j) = t(i, j)
        Next j
    End If
Next i
1 With [A29] 'cellule à adapter éventuellement
    If n Then .Resize(n, ncol) = t
    .Offset(n).Resize(Rows.Count - n - .Row + 1, ncol).Delete xlUp 'RAZ en dessous
End With

'mise en forme police et alignement
    Range("A29:AK1500").Font.Size = 8
    Range("A29:AK1500").Font.Name = "Calibri   "
    With Range("A29:AK1500")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
 
'mise en forme format colonnes
    Range("B29:B1500").Select
    Selection.NumberFormat = "m/d/yyyy"

    Range("F29:F1500,H29:H1500,O29:O1500,AJ29:AK1500").Select
    Selection.NumberFormat = "h:mm;@"

    Range("N29:N1500,W29:W1500,X29:X1500").Select
    Selection.NumberFormat = "0.00"

    Range("A24:A24").Select

End Sub