Sub mljk()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each sh In Sheets
If sh.Name <> "base" And sh.Name <> "Adresse" And sh.Name <> "test" Then sh.Delete
Next sh
For j = 1 To 4
k = 0
nom = Sheets("base").Range("e1").Offset(0, j)
commande = Sheets("base").Range("e2").Offset(0, j)
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = nom
With Sheets(nom)
.Range("a3") = "Nom"
.Range("b3") = nom
.Range("a4") = "N°Commande"
.Range("b4") = commande
.Range("a5") = "Adresse"
.Range("b5") = adresse(nom)
.Range("a7") = "Qté"
.Range("b7") = "designation"
.Range("c7") = "couleur"
.Range("d7") = "prix"
.Range("e7") = "Total"
For i = 4 To 15
If Sheets("base").Cells(i, j + 5) <> 0 Then
.Range("a8").Offset(k, 0) = Sheets("base").Cells(i, j + 5)
.Range("b8").Offset(k, 0) = Sheets("base").Range("a" & i)
.Range("c8").Offset(k, 0) = Sheets("base").Range("b" & i)
.Range("d8").Offset(k, 0) = Sheets("base").Range("c" & i)
.Range("e8").Offset(k, 0).Formula = "=a" & 8 + k & "*d" & 8 + k
k = k + 1
End If
Next i
.Range("d8").Offset(k, 0) = "TOTAL"
.Range("e8").Offset(k, 0).Formula = "=sum(e8:e" & 7 + k & ")"
End With
Next j
End Sub
Function adresse(nom)
Set WF = WorksheetFunction
adresse = WF.Index(Sheets("Adresse").Range("b3:b6"), _
WF.Match(nom, Sheets("Adresse").Range("a3:a6"), 0))
End Function