Sub CombinerREf_Et_Sites()
Dim tRefs, tSites
Dim tRes() As Variant
Dim iRef As Long, iSite As Long, cpt As Long
With Sheets("Réf")
tRefs = .Range("A5:A" & .Cells(.Rows.Count, 1).End(xlUp).Row).Value
End With
With Sheets("Sites")
tSites = .Range("A6:A" & .Cells(.Rows.Count, 1).End(xlUp).Row).Value
End With
ReDim Preserve tRes(1 To 2, 1 To UBound(tSites) * UBound(tRefs))
For iSite = 1 To UBound(tSites)
For iRef = 1 To UBound(tRefs)
cpt = cpt + 1
tRes(1, cpt) = "'" & Format(tSites(iSite, 1), "000")
tRes(2, cpt) = tRefs(iRef, 1)
Next iRef
Next iSite
Sheets("Feuil3").Range("A5").Resize(cpt, 2).Value = Application.Transpose(tRes)
End Sub