1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
| Sub Marchés_x_Produits()
Dim LR As Long, i As Long, j As Long, NbProduits As Long, MaPlage As Range
Application.ScreenUpdating = False
'Création de la liste Marchés x Produits dans l'onglet "RESULTAT"
With Sheets("RESULTAT")
'dernière ligne
LR = Sheets("MARCHE").Cells(Rows.Count, 5).End(xlUp).Row
'-----
NbProduits = Sheets("Product List").Cells(Rows.Count, 5).End(xlUp).Row - 10
Set MaPlage = Sheets("Product List").Range("E11:AA" & NbProduits + 10)
'espace ou l'on applique la boucle
For i = 4 To LR
'----
.Range(.Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0), .Cells(.Rows.Count, 2).End(xlUp).Offset(NbProduits, 2)).Value = _
Sheets("MARCHE").Range("F" & i & ":H" & i).Value
MaPlage.Copy .Cells(.Rows.Count, 5).End(xlUp).Offset(1, 0)
Next i
'Déversement des ces données créées dans chaque onglet marché correspondant.
LR = .Cells(Rows.Count, 2).End(xlUp).Row
For i = 11 To LR
If .Cells(i, 3).Value = "UK" Then
.Range("B" & i & ":AA" & i).Copy Sheets("TAD UK").Range("B1048576").End(xlUp).Offset(1, 0)
ElseIf .Cells(i, 3).Value = "EUROPE HOTEL" Then
.Range("B" & i & ":AA" & i).Copy Sheets("TAD Europe Hotel").Range("B1048576").End(xlUp).Offset(1, 0)
ElseIf .Cells(i, 3).Value = "MOPO" Then
.Range("B" & i & ":AA" & i).Copy Sheets("TAD MOPO").Range("B1048576").End(xlUp).Offset(1, 0)
ElseIf .Cells(i, 3).Value = "FR/BNL" Then
.Range("B" & i & ":AA" & i).Copy Sheets("TAD FR BNL").Range("B1048576").End(xlUp).Offset(1, 0)
ElseIf .Cells(i, 3).Value = "EUROPE TR" Then
.Range("B" & i & ":AA" & i).Copy Sheets("TAD EUROPE TR").Range("B1048576").End(xlUp).Offset(1, 0)
ElseIf .Cells(i, 3).Value = "CANADA" Then
.Range("B" & i & ":AA" & i).Copy Sheets("TAD CANADA").Range("B1048576").End(xlUp).Offset(1, 0)
ElseIf .Cells(i, 3).Value = "BU SUD" Then
.Range("B" & i & ":AA" & i).Copy Sheets("TAD BU SUD").Range("B1048576").End(xlUp).Offset(1, 0)
ElseIf .Cells(i, 3).Value = "BU NORD" Then
.Range("B" & i & ":AA" & i).Copy Sheets("TAD BU NORD").Range("B1048576").End(xlUp).Offset(1, 0)
ElseIf .Cells(i, 3).Value = "BPI US" Then
.Range("B" & i & ":AA" & i).Copy Sheets("TAD BPI").Range("B1048576").End(xlUp).Offset(1, 0)
ElseIf .Cells(i, 3).Value = "ASIE" Then
.Range("B" & i & ":AA" & i).Copy Sheets("TAD ASIE").Range("B1048576").End(xlUp).Offset(1, 0)
ElseIf .Cells(i, 3).Value = "AMERIQUE" Then
.Range("B" & i & ":AA" & i).Copy Sheets("TAD AMERIQUES").Range("B1048576").End(xlUp).Offset(1, 0)
End If
Next i
End With
MsgBox "Traitement terminé avec succès.", vbInformation + vbOKOnly, "<<<(^v^)>>>"
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub |
Partager