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
| Sub NouveauxCodes()
Application.ScreenUpdating = False
'Définition des variables
Dim Traite As Range
Dim GLdate As Variant
Dim Partner As Variant
Dim Product As Variant
Dim LocaReserve As Variant
Dim LocalDescription As Variant
Dim Devise As Variant
Dim Montant As Variant
Dim Nouveau As Range
Dim NouveauGLdate As Variant
Dim NouveauPartner As Variant
Dim NouveauProduct As Variant
Dim NouveauCostCenter As Variant
Dim NouveauLocalDescription As Variant
Dim NouveauDevise As Variant
Dim NouveauMontant As Variant
'Définition des feuilles
Dim ShPdts As Worksheet
Dim ShGLEMTN As Worksheet
Set ShPdts = Worksheets("produits_emissions AMUNDI")
Set ShGLEMTN = Worksheets("02-03 - GL")
ShGLEMTN.Range("R3:Y1048576").Clear
'Balayage colonne Alimenté
For Each Traite In ShGLEMTN.Range("O2", ShGLEMTN.Range("O1048576").End(xlUp)).Cells
'Valeur de la ligne
Set GLdate = Traite.Offset(0, -12)
Set Partner = Traite.Offset(0, -9)
Set Product = Traite.Offset(0, -7)
Set LocaReserve = Traite.Offset(0, -6)
Set LocalDescription = Traite.Offset(0, -5)
Set Devise = Traite.Offset(0, -4)
Set Montant = Traite.Offset(0, -2)
'Condition de nouveaux codes
If Traite <> "Oui" Then
ShGLEMTN.Range("R1048576").End(xlUp).Offset(1, 0).NumberFormat = "dd/mm/yyyy"
ShGLEMTN.Range("R1048576").End(xlUp).Offset(1, 0).Value = GLdate
ShGLEMTN.Range("S1048576").End(xlUp).Offset(1, 0).Value = Partner
ShGLEMTN.Range("U1048576").End(xlUp).Offset(1, 0).Value = Product
ShGLEMTN.Range("V1048576").End(xlUp).Offset(1, 0).Value = LocaReserve
ShGLEMTN.Range("W1048576").End(xlUp).Offset(1, 0).Value = LocalDescription
ShGLEMTN.Range("X1048576").End(xlUp).Offset(1, 0).Value = Devise
ShGLEMTN.Range("Y1048576").End(xlUp).Offset(1, 0).Value = Montant
ShGLEMTN.Range("AB1").Copy
ShGLEMTN.Range("T1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulas
End If
Next Traite
ShGLEMTN.Range("T2:T1048576").Copy
ShGLEMTN.Range("T2:T1048576").PasteSpecial xlPasteValues
'Balayage des nouveaux codes produits
For Each Nouveau In ShGLEMTN.Range("V3", ShGLEMTN.Range("V1048576").End(xlUp)).Cells
'Valeur de la ligne
Set NouveauGLdate = Nouveau.Offset(0, -4)
Set NouveauPartner = Nouveau.Offset(0, -3)
Set NouveauCostCenter = Nouveau.Offset(0, -2)
Set NouveauProduct = Nouveau.Offset(0, -1)
Set NouveauLocalDescription = Nouveau.Offset(0, 1)
Set NouveauDevise = Nouveau.Offset(0, 2)
Set NouveauMontant = Nouveau.Offset(0, 3)
'Alimentation produits
ShPdts.Range("A5").End(xlDown).Offset(1, 0).Value = Nouveau
ShPdts.Range("A5").End(xlDown).Offset(0, 1).Value = NouveauPartner
ShPdts.Range("A5").End(xlDown).Offset(0, 2).Value = NouveauProduct
ShPdts.Range("A5").End(xlDown).Offset(0, 5).Value = NouveauLocalDescription
ShPdts.Range("A5").End(xlDown).Offset(0, 3).Value = NouveauCostCenter
ShPdts.Range("A5").End(xlDown).Offset(0, 6).Value = NouveauDevise
ShPdts.Range("A5").End(xlDown).Offset(0, 7).Value = NouveauMontant
Next Nouveau
Call Operationnews
End Sub |
