Sub Ventiler()
Const Source = "Feuil1", Cible = "Feuil2", Cellule = "a1"
Dim Fsource As Worksheet, Fcible As Worksheet, CelluleCible As Range
Dim Vals, derlig&, dercol&, Res(), j&, i&, n&
Set Fsource = Worksheets(Source): Set Fcible = Worksheets(Cible)
Set CelluleCible = Fcible.Range(Cellule)
With Fsource
derlig = .Cells(.Rows.Count, "a").End(xlUp).Row
If derlig = 1 Then
MsgBox "Pas de données en ligne -> échec !", vbCritical
Exit Sub
End If
dercol = .Cells(1, Columns.Count).End(xlToLeft).Column
If dercol < 3 Then
MsgBox "Pas de données en colonne -> échec !", vbCritical
Exit Sub
End If
Vals = .Range(.Cells(1, "a"), .Cells(derlig, dercol))
ReDim Res(1 To (derlig - 1) * (dercol - 2), 1 To 4)
For i = 2 To derlig
For j = 3 To dercol
n = n + 1
Res(n, 1) = Vals(1, j): Res(n, 2) = Vals(i, 1)
Res(n, 3) = Vals(i, 2): Res(n, 4) = Vals(i, j)
Next j
Next i
End With
CelluleCible.CurrentRegion.ClearContents
CelluleCible.Resize(UBound(Res), UBound(Res, 2)) = Res
Application.Goto CelluleCible, True
End Sub