XL 2019 planning mensuel et menu deroulant

Private Sub WorkSheet_Change(ByVal Target As Range)
Dim flag1 As Boolean, flag2 As Boolean, annee$, mois$, ville$, matrice
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Resume Next
'---stockage des données---
If Intersect(Target, [D10:AH14]) Is Nothing Then flag1 = True: Application.Undo 'annule la modification
If Application.CountA([C2:C4]) = 3 Then
    flag2 = True
    annee = [C2]
    mois = [C3]
    ville = Replace([C4], " ", "_")
    matrice = [D10:AH14].Formula
End If
If flag1 Then Application.Undo 'rétablit la modification
If flag2 Then ThisWorkbook.Names.Add ville & "_" & annee & "_" & mois, matrice 'nom défini
'---récupération des données---
[D10:AH14].ClearContents 'RAZ
matrice = Evaluate(Replace([C4], " ", "_") & "_" & [C2] & "_" & [C3])
If Not IsError(matrice) Then [D10:AH14] = matrice
Application.EnableEvents = True
End Sub

Sub Test()
Dim matrice, ville, annee, mois
matrice = Sheets("Calendrier").[D10:AH14].Formula
With Sheets("Sources")
    For Each ville In .Range("M3:M14")
        For Each annee In .Range("F3:F14")
            For Each mois In .Range("H3:H14")
                ThisWorkbook.Names.Add Replace(ville, " ", "_") & "_" & annee & "_" & mois, matrice 'nom défini
    Next mois, annee, ville
End With
End Sub

<definedName name="AMBERT_THIERS_2025_AOUT">{"G","","","","","","","J","","","","","","N","","","","","","","","","","","","J","","","","","";"N","","","","","","J","","","","","","","","","","","","G","","","","","","","","","","","","";"","J","","","","","","","G","","","G","","","","","","","","","","","","","","","","","","","";"","","","","","","","","","","","","","","G","","","","","","G","","","","","","","","","","";"","","","","","J","","","","","","","J","","","","","","","","","","","","","","","","","",""}</definedName>
<definedName name="AMBERT_THIERS_2025_AVRIL">{"G","","","","","","","J","","","","","","N","","","","","","","","","","","","J","","","","","";"N","","","","","","J","","","","","","","","","","","","G","","","","","","","","","","","","";"","J","","","","","","","G","","","G","","","","","","","","","","","","","","","","","","","";"","","","","","","","","","","","","","","G","","","","","","G","","","","","","","","","","";"","","","","","J","","","","","","","J","","","","","","","","","","","","","","","","","",""}</definedName>

Sub Test()
Dim a, ub%, F As Worksheet, ville, annee, mois, dercol%, c As Range
a = Array("J", "G", "N", "")
ub = UBound(a)
Set F = Sheets("Calendrier")
Application.ScreenUpdating = False
Application.EnableEvents = False
With Sheets("Sources")
    For Each ville In .Range("M3:M14")
        F.Range("C4") = ville
        For Each annee In .Range("F3:F14")
            F.Range("C2") = annee
            For Each mois In .Range("H3:H14")
                F.Range("C3") = mois
                With F.Range("D10:AH14")
                    dercol = 3 + Application.CountIf(.Rows(0), "><")
                    .ClearContents
                    For Each c In .Cells
                        If c.Column <= dercol Then c = a(Application.RandBetween(0, ub)) 'ALEA.ENTRE.BORNES
                    Next c
                    ThisWorkbook.Names.Add Replace(ville, " ", "_") & "_" & annee & "_" & mois, .Formula 'nom défini
                End With
    Next mois, annee, ville
End With
Application.EnableEvents = True
End Sub