Bonsoir à tous,
Tes données à partir de A1, avec une ligne d'en-têtes
Option Explicit
Sub test()
Dim a, b(), i As Long, j As Long, n As Long, k As Byte
Application.ScreenUpdating = False
a = Sheets(1).Range("a1").CurrentRegion.Value
ReDim b(1 To (UBound(a, 1) - 1) * 3 + 1, 1 To 3)
b(1, 1) = "Noms": b(1, 2) = "Tel": b(1, 3) = "Ref"
n = 1
For i = 2 To UBound(a, 1)
For j = 2 To UBound(a, 2) - 1
If a(i, j) <> "" Then
n = n + 1: k = k + 1
b(n, 1) = a(i, 1)
b(n, 2) = a(i, j)
b(n, 3) = a(i, 5) & "-" & k
End If
Next
k = 0
Next
'Restitution en Feuil2
With Sheets(2)
.Cells.Clear
With .Cells(1)
.Resize(n, 3).Value = b
With .CurrentRegion
With .Rows(1)
.BorderAround Weight:=xlThin
.Interior.ColorIndex = 44
End With
.Font.Name = "calibri"
.Font.Size = 10
.BorderAround Weight:=xlThin
.Borders(xlInsideVertical).Weight = xlThin
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
'.Columns.AutoFit
End With
End With
End With
Application.ScreenUpdating = True
End Sub
klin89