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
| Sub InsertPivotTableMast ()
‘Declare Variables
Dim PSheet As Worksheet
Dim DSheetMast as Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PrangeMast As Range
Dim LastRowMast As Long
Dim LastColMast As Long
‘Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets (“PivotTable”).Delete
Sheets.Add After:=Worksheets (“Matisse”)
ActiveSheet.Name = “PivotTable”
Application.DisplayAlerts = True
Set PSheet = Worksheets (“PivotTable”)
Set DSheetMast = Worksheets (“Mast”)
‘Define Data Range
LastRowMast = DSheetMast.Cells (Rows.Count, 1).End (xlUp).Row
LastRowMast = DSheetMast.Cells (1, Columns.Count).End(xlToLeft).Column
Set PRangeMast = DSheetMast.Cells (1, 1).Resize (LastRowMast, LastColMast)
‘Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRangeMast). _
CreatePivotTable (TableDestination:=PSheet.Cells (1, 1), _
TableName:=”MastPivotTable”)
‘Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells (1, 1), TableName:=”MastPivotTable”)
‘Insert Row Fields
With ActiveSheet.PivotTables (“MastPivotTable”).PivotFields (“ValueDate”)
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables (“MastPivotTable”).PivotFields (“M_CURRENCY”)
.Orientation = wlRowField
.Position = 2
End With
With ActiveSheet.PivotTables (“MastPivotTable”).PivotFields (“OriginalRef”)
.Orientation = wlRowField
.Position = 3
End With
‘Insert Data Field
With ActiveSheet.PivotTables (“MastPivotTable”).PivotFields “Amount”)
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = “#,##0.00”
.Name =”AmountSum”
End With
End Sub
Sub InsertPivotTableMatisse ()
‘Declare Variables
Dim PSheet As Worksheet
Dim DSheetMatisse as Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PrangeMatisse As Range
Dim LastRowMatisse As Long
Dim LastColMatisse As Long
‘Insert a New Blank Worksheet
ActiveSheet.Name = “PivotTable”
Application.DisplayAlerts = True
Set PSheet = Worksheets (“PivotTable”)
Set DSheetMatisse = Worksheets (“Matisse”)
‘Define Data Range
LastRowMatisse = DSheetMatisse.Cells (Rows.Count, 1).End (xlUp).Row
LastRowMatisse = DSheetMatisse.Cells (1, Columns.Count).End(xlToLeft).Column
Set PRangeMatisse = DSheetMatisse.Cells (1, 1).Resize (LastRowMatisse, LastColMatisse)
‘Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRangeMatisse). _
CreatePivotTable (TableDestination:=PSheet.Cells (1, 4), _
TableName:=” MatissePivotTable”)
‘Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells (1, 1), TableName:=” MatissePivotTable”)
‘Insert Row Fields
With ActiveSheet.PivotTables (“MatissePivotTable”).PivotFields (“Theorical value date”)
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables (“MatissePivotTable”).PivotFields (“Currency”)
.Orientation = wlRowField
.Position = 2
End With
With ActiveSheet.PivotTables (“MatissePivotTable”).PivotFields (“External ref. 1”)
.Orientation = wlRowField
.Position = 3
End With
‘Insert Data Field
With ActiveSheet.PivotTables (“MatissePivotTable”).PivotFields “Amount”)
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = “#,##0.00”
.Name =”AmountSum”
End With
End Sub |
Macro de vérification de données identiques par référence
Répondre avec citation
Envoyé par DiGio
Partager