Un grand merci à Gmb
Tout est ok
Quant à moi j'en était là... avec encore un bug sur la seconde boucle ?
Sub MiseEnPage()
Dim c As Long, i As Byte
Dim listentree As Range, listplat As Range, listgarniture As Range, listfromage As Range, listdessert As Range
'Copier la feuille modèle
Sheets("MEP").Select
Sheets("MEP").Copy After:=Sheets(3)
Sheets(4).Name = "Menus Listés"
'Mettre les dates
For c = 1 To 10 Step 3
Sheets(4).Cells(5, c).Value = Sheets("Menu").Cells(5, c / 3 * 5 - 1).Value
Next c
'Insérer les listes déroulantes de choix
For i = 3 To 12 Step 3
For c = 1 To 10 Step 3
With Sheets("menu")
Set listentree = .Range(.Cells(7, c / 3 * 5 - 1), .Cells(12, c / 3 * 5 - 1)) 'entree
Set listplat = .Range(.Cells(14, c / 3 * 5 - 1), .Cells(19, c / 3 * 5 - 1)) 'plat
Set listgarniture = .Range(.Cells(21, c / 3 * 5 - 1), .Cells(24, c / 3 * 5 - 1)) 'garniture
Set listfromage = .Range(.Cells(26, c / 3 * 5 - 1), .Cells(28, c / 3 * 5 - 1)) 'fromage
Set listdessert = .Range(.Cells(30, c / 3 * 5 - 1), .Cells(34, c / 3 * 5 - 1)) 'dessert
End With
'1er choix entrée
Cells(6, i).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=menu!" & listentree.Address 'entrée
'2nd choix entrée
Cells(7, i).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=menu!" & listentree.Address 'entrée
'1er choix plat
Cells(8, i).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=menu!" & listplat.Address 'plat
Cells(9, i).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=menu!" & listgarniture.Address 'garniture
'2nd choix plat
Cells(10, i).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=menu!" & listplat.Address 'plat
Cells(11, i).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=menu!" & listgarniture.Address 'garniture
'choix fromage
Cells(12, i).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=menu!" & listfromage.Address 'fromage
'choix dessert
Cells(13, i).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=menu!" & listdessert.Address 'dessert
Set listentree = Nothing 'entree
Set listplat = Nothing 'plat
Set listgarniture = Nothing 'garniture
Set listfromage = Nothing 'fromage
Set listdessert = Nothing 'dessert
Next c
Next i
End Sub
Merci
Leakim