Bonjour à tous,

J'ai mis au point une petite macro pour sortir un graphique croisé dynamique j'ai utiliser un filtre ("slicer") sur celui-ci mais ça reste pas très ergonomique à l'usage je voudrais pouvoir passer d'une donné traité à une autre simplement avec les flèches.
Je souhaiterais y ajouter un Form control type list box ou spin button mais j'ai de la procédure pour lié celui-ci au tableau.
En vous remerciant par avance,

Cdlt,

Benjamin

Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
Sub Mixtsizing()
'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
Dim p As PivotField
 
'Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotChart").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotChart"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotChart")
Set DSheet = Worksheets("PO files")
 
'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
 
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="Mixt sizing PO")
 
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="Mixt sizing PO")
 
'Insert Row Fields & Column
 
With ActiveSheet.PivotTables("Mixt sizing PO").PivotFields("Grid Value")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Mixt sizing PO").PivotFields("Article")
.Orientation = xlColumnField
.Position = 1
End With
 
 
With ActiveSheet.PivotTables("Mixt sizing PO").PivotFields("Requested Delivery Date")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("Mixt sizing PO").PivotFields("lead time")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("Mixt sizing PO").PivotFields("Customer Name")
.Orientation = xlPageField
.Position = 1
End With
 
'Insert Data Field
With ActiveSheet.PivotTables("Mixt sizing PO").PivotFields("Requested Quantity")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Quantity"
End With
 
'Format Pivot Table
 
ActiveSheet.PivotTables("Mixt sizing PO").RowAxisLayout xlTabularRow
ActiveSheet.PivotTables("Mixt sizing PO").RepeatAllLabels xlRepeatLabels
ActiveSheet.PivotTables("Mixt sizing PO").TableStyle2 = "PivotStyleDark9"
 
For Each p In ActiveSheet.PivotTables(1).PivotFields 'delt subtotal
    If p.Orientation = 1 Then p.Subtotals = Array(False, False, False, False, _
        False, False, False, False, False, False, False, False)
Next p
 
    Range("J18").Select
    ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
    ActiveChart.SetSourceData Source:=Range(ActiveSheet.PivotTables("Mixt sizing PO"))
    ActiveWorkbook.SlicerCaches.Add2(ActiveChart.PivotLayout.PivotTable, "Article") _
        .Slicers.Add ActiveSheet, , "Article 1", "Article", 211.5, 306.75, 144, 187.5
    ActiveSheet.Shapes.Range(Array("Article 1")).Select
    ActiveSheet.Shapes("Article 1").IncrementLeft 363
    ActiveSheet.Shapes("Article 1").IncrementTop -71.25
    With ActiveWorkbook.SlicerCaches("Slicer_Article1")
 
    End With
 
End Sub