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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
|
Sub MiseAJourTableauDeBord()
Dim wsOutbound As Worksheet
Dim wsRevenue As Worksheet
Dim wsMainOeuvre As Worksheet
Dim wsCarburant As Worksheet
Dim wsHabillage As Worksheet
Dim wsReachStacker As Worksheet
Dim wsChariot As Worksheet
Dim wsFumigation As Worksheet
Dim wsAutre As Worksheet
Dim wsLocationMagasin As Worksheet
Dim wsEmpotage As Worksheet
Dim wsTableauDeBord As Worksheet
' Récupérer les références des feuilles de calcul
Set wsOutbound = ThisWorkbook.Sheets("Outbound")
Set wsRevenue = ThisWorkbook.Sheets("Revenue")
Set wsMainOeuvre = ThisWorkbook.Sheets("Main d'œuvre")
Set wsCarburant = ThisWorkbook.Sheets("Carburant")
Set wsHabillage = ThisWorkbook.Sheets("Habillage")
Set wsReachStacker = ThisWorkbook.Sheets("Reach Stacker")
Set wsChariot = ThisWorkbook.Sheets("Chariot")
Set wsFumigation = ThisWorkbook.Sheets("Fumigation")
Set wsAutre = ThisWorkbook.Sheets("Autre")
Set wsLocationMagasin = ThisWorkbook.Sheets("Location Magasin")
Set wsEmpotage = ThisWorkbook.Sheets("Empotage")
Set wsTableauDeBord = ThisWorkbook.Sheets("Tableau de Bord")
' Récupérer les données de la feuille Outbound
Dim outboundLastRow As Long
outboundLastRow = wsOutbound.Cells(wsOutbound.Rows.Count, "H").End(xlUp).Row
Dim outboundRange As Range
Set outboundRange = wsOutbound.Range("H2:I" & outboundLastRow)
' Mettre à jour la colonne J de la feuille Outbound
Dim cell As Range
For Each cell In outboundRange
Select Case cell.Value
Case "Africa Sourcing"
Select Case cell.Offset(0, 1).Value
Case "Mise à Fob Sac"
cell.Offset(0, 2).Value = 31500
Case "Mise à Fob Vrac"
cell.Offset(0, 2).Value = 33500
Case "Mise à Fob Conventionnel"
cell.Offset(0, 2).Value = 20500
Case "Rechargement"
cell.Offset(0, 2).Value = 1000
Case "Logistique"
cell.Offset(0, 2).Value = 12000
Case "Manutention", "Autre"
cell.Offset(0, 2).Value = 50000
End Select
Case "Saco"
Select Case cell.Offset(0, 1).Value
Case "Mise à Fob Sac"
cell.Offset(0, 2).Value = 30000
Case "Mise à Fob Vrac"
cell.Offset(0, 2).Value = 31000
Case "Mise à Fob Conventionnel"
cell.Offset(0, 2).Value = 19500
Case "Rechargement"
cell.Offset(0, 2).Value = 1000
Case "Logistique"
cell.Offset(0, 2).Value = 12000
Case "Manutention", "Autre"
cell.Offset(0, 2).Value = 50000
End Select
Case "Autre"
Select Case cell.Offset(0, 1).Value
Case "Mise à Fob Sac"
cell.Offset(0, 2).Value = 35000
Case "Mise à Fob Vrac"
cell.Offset(0, 2).Value = 37000
Case "Mise à Fob Conventionnel"
cell.Offset(0, 2).Value = 23000
Case "Rechargement"
cell.Offset(0, 2).Value = 1000
Case "Logistique"
cell.Offset(0, 2).Value = 12000
Case "Manutention", "Autre"
cell.Offset(0, 2).Value = 50000
End Select
End Select
Next cell
' Mettre à jour le tableau de bord
Dim dashboardLastRow As Long
dashboardLastRow = wsTableauDeBord.Cells(wsTableauDeBord.Rows.Count, "A").End(xlUp).Row
Dim dashboardRange As Range
Set dashboardRange = wsTableauDeBord.Range("A2:K" & dashboardLastRow)
For Each cell In dashboardRange
Select Case cell.Value
Case "Revenue"
cell.Offset(0, 1).Formula = "=SUMIF(Revenue!$A$2:$A$" & revenueLastRow & ",$A2,Revenue!$C$2:$C$" & revenueLastRow & ")"
Case "Main d'œuvre"
cell.Offset(0, 1).Formula = "=SUMIF('Main d''œuvre'!$A$2:$A$" & mainOeuvreLastRow & ",$A2,'Main d''œuvre'!$C$2:$C$" & mainOeuvreLastRow & ")"
Case "Carburant"
cell.Offset(0, 1).Formula = "=SUMIF(Carburant!$A$2:$A$" & carburantLastRow & ",$A2,Carburant!$C$2:$C$" & carburantLastRow & ")"
Case "Habillage"
cell.Offset(0, 1).Formula = "=SUMIF(Habillage!$A$2:$A$" & habillageLastRow & ",$A2,Habillage!$C$2:$C$" & habillageLastRow & ")"
Case "Reach Stacker"
cell.Offset(0, 1).Formula = "=SUMIF('Reach Stacker'!$A$2:$A$" & reachStackerLastRow & ",$A2,'Reach Stacker'!$C$2:$C$" & reachStackerLastRow & ")"
Case "Chariot"
cell.Offset(0, 1).Formula = "=SUMIF(Chariot!$A$2:$A$" & chariotLastRow & ",$A2,Chariot!$C$2:$C$" & chariotLastRow & ")"
Case "Fumigation"
cell.Offset(0, 1).Formula = "=SUMIF(Fumigation!$A$2:$A$" & fumigationLastRow & ",$A2,Fumigation!$C$2:$C$" & fumigationLastRow & ")"
Case "Autre"
cell.Offset(0, 1).Formula = "=SUMIF(Autre!$A$2:$A$" & autreLastRow & ",$A2,Autre!$C$2:$C$" & autreLastRow & ")"
Case "Location Magasin"
cell.Offset(0, 1).Formula = "=SUMIF('Location Magasin'!$A$2:$A$" & locationMagasinLastRow & ",$A2,'Location Magasin'!$C$2:$C$" & locationMagasinLastRow & ")"
Case "Empotage"
cell.Offset(0, 1).Formula = "=SUMIF(Empotage!$A$2:$A$" & empotageLastRow & ",$A2,Empotage!$C$2:$C$" & empotageLastRow & ")"
End Select
Next cell
End Sub |
MON Code VBA ne fonction pas sur mon tableau de bord
Répondre avec citation
Partager