Sub Macro1()
Dim i As Byte 'déclare la variable i (Incrément)
Dim cel As Range 'déclare la variable cel (CELlule)
Dim liste As String 'déclare la variable liste
'création de la liste dynamique sur plusieurs onglets
For i = 1 To 3 'boucle '1 : sur trois onglets (à adapter à ton cas)
With Sheets(i) 'prend en compte l'onglet de la boucle 1
'boucle 2 : sur toutes les cellules éditées cel de la colonne I de l'onglet (à adapter à ton cas)
For Each cel In .Range("I1:I" & .Cells(Application.Rows.Count, 9).End(xlUp).Row)
liste = IIf(liste = "", cel.Value & ",", liste & Chr(13) & cel.Value & ",") 'définit la liste
Next cel 'prochaine cellule de la boucle 2
End With 'fin de la prise en compte de l'onglet de la boucle 1
Next i 'prochain onglet de la boucle 1
'validation de données sur la plage A1:A10 de l'onglet Feuil1 (à adapter à ton cas)
With Sheets("Feuil1").Range("A1:A10").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=liste
End With
End Sub