Bonsoir kasper2004,
A tester, je n'ai pas formaté les 3 dernières colonnes lors de la restitution :
La restitution s'effectue sur une feuille nouvellement créée.
Option Explicit
Sub test()
Dim a, b(), i As Long, j As Long, n As Long
a = Sheets("Données").Range("a3").CurrentRegion.Value
ReDim b(1 To ((UBound(a, 1) - 3) * ((UBound(a, 2) - 5) / 5)) + 1, 1 To 12)
n = 1
b(n, 1) = a(3, 1): b(n, 2) = a(3, 2): b(n, 3) = a(3, 3)
b(n, 4) = a(3, 4): b(n, 5) = a(3, 5): b(n, 6) = "Numéro Client"
b(n, 7) = "Nom Client": b(n, 8) = a(3, 6): b(n, 9) = a(3, 7)
b(n, 10) = a(3, 8): b(n, 11) = a(3, 9): b(n, 12) = a(3, 10)
For j = 6 To UBound(a, 2) Step 5
For i = 4 To UBound(a, 1)
n = n + 1
b(n, 1) = a(i, 1): b(n, 2) = a(i, 2)
b(n, 3) = a(i, 3): b(n, 4) = a(i, 4)
b(n, 5) = a(i, 5)
b(n, 6) = a(1, j): b(n, 7) = a(2, j)
b(n, 8) = a(i, j): b(n, 9) = a(i, j + 1)
b(n, 10) = a(i, j + 2): b(n, 11) = a(i, j + 3)
b(n, 12) = a(i, j + 4)
Next
Next
Application.ScreenUpdating = False
On Error Resume Next
Application.DisplayAlerts = False
Sheets("restitution").Delete
Sheets.Add().Name = "restitution"
On Error GoTo 0
With Sheets("restitution").Range("a1")
With .Resize(UBound(b, 1), UBound(b, 2))
.Value = b
.Font.Name = "calibri"
.Font.Size = 10
.BorderAround Weight:=xlThin
.Borders(xlInsideVertical).Weight = xlThin
.VerticalAlignment = xlCenter
With .Rows(1)
.BorderAround Weight:=xlThin
.Interior.ColorIndex = 43
.HorizontalAlignment = xlCenter
End With
.Columns.AutoFit
End With
End With
Application.ScreenUpdating = True
End Sub
klin89