Currently, I create VBA coding to insert pivot table. However, I find that the table created by coding cannot use slicer to connect two different pivot table. Can anyone help? Thanks!
Attachment 15728
Printable View
Currently, I create VBA coding to insert pivot table. However, I find that the table created by coding cannot use slicer to connect two different pivot table. Can anyone help? Thanks!
Attachment 15728
The slicer needs to share the same pivot cache; you've created 2 pivot caches as well as 2 pivot tables, so something along the lines of:
Code:Sub Insert_Pivot_tables()
Set PC = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Sheet1!R1C1:R9C3") 'create one pivotcache.
Set PT1 = PC.CreatePivotTable(TableDestination:="Sheet1!R15C1") 'create pivot table 1 (name isn't important) using cache just created.
Set PT2 = PC.CreatePivotTable(TableDestination:="Sheet1!R15C5") 'create pivot table 2 (name isn't important) using the same cache.
'then you can programmatically add the slicer and link the two pivot tables with it:
Set SC = ActiveWorkbook.SlicerCaches.Add(PT1, "SN") 'create one slicer cache linked to the first pivot table.
SC.Slicers.Add ActiveSheet, , "SN", "SN", 210.75, 549.75, 144, 198.75 'add slicer to that slicer cache.
SC.PivotTables.AddPivotTable (PT2) 'add 2nd pivot table to slicer cache.
End Sub
It works! Thank you very much for your teaching!
Hi, the above code works for only 2 pivot tables.
I need code for connecting multiple pivot tables which are named say 10A,10B,10C etc., to a single slicer (Type).
my recorded macro is showing error, below is the recorded code
Code:ActiveWorkbook.SlicerCaches("Slicer_Type").PivotTables. _
AddPivotTable (ActiveSheet.PivotTables("10A"))
ActiveWorkbook.SlicerCaches("Slicer_Type").PivotTables. _
AddPivotTable (ActiveSheet.PivotTables("10B"))
ActiveWorkbook.SlicerCaches("Slicer_Type").PivotTables. _
AddPivotTable (ActiveSheet.PivotTables("10C"))
ActiveWorkbook.SlicerCaches("Slicer_Type").PivotTables. _
AddPivotTable (ActiveSheet.PivotTables("10D"))
It works for 3 here:Even your code works if the pivot tables concerned all use the same PivotCache.Code:Sub Insert_Pivot_tables()
Set PC = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Sheet1!R1C1:R9C3") 'create one pivotcache.
Set PT1 = PC.CreatePivotTable(TableDestination:="Sheet1!R15C1") 'create pivot table 1 (name isn't important) using cache just created.
Set PT2 = PC.CreatePivotTable(TableDestination:="Sheet1!R15C5") 'create pivot table 2 (name isn't important) using the same cache.
Set PT3 = PC.CreatePivotTable(TableDestination:="Sheet1!R15C10") 'create pivot table 3 (name isn't important) using the same cache.
'then you can programmatically add the slicer and link the two pivot tables with it:
Set SC = ActiveWorkbook.SlicerCaches.Add(PT1, "SN") 'create one slicer cache linked to the first pivot table.
SC.Slicers.Add ActiveSheet, , "SN", "SN", 210.75, 549.75, 144, 198.75 'add slicer to that slicer cache.
SC.PivotTables.AddPivotTable (PT2) 'add 2nd pivot table to slicer cache.
SC.PivotTables.AddPivotTable (PT3) 'add 3rd pivot table to slicer cache.
End Sub
Thanks for your time p45cal.
But I have already created multiple pivot tables with same cache and named them all say 10A,10B,10C and so on.. i only need the code for slicer report connections to sync them all up.. with the above code when i use pivot table name in the slicer like instead of
thisCode:SC.PivotTables.AddPivotTable (PT3)
then the code wont run. so im looking code for only connecting existing pivot tables to a new slicer.Code:SC.PivotTables.AddPivotTable (10A)
It would probably need to be something like:
SC.PivotTables.AddPivotTable (ActiveSheet.PivotTables("10A"))
If that still fails (what's the error mesage?) then try running this little snippet:It will run through the pivot tables on the active sheet, selecting them one at a time and display their name and cache index in a pop-up message box.Code:Sub test()
For Each pt In ActiveSheet.PivotTables
pt.TableRange2.Select
MsgBox "Cache Index: " & pt.CacheIndex & vbLf & "Pivot table name: " & pt.Name
Next pt
End Sub
Make sure that the cache index is the same for every pivot table you want to be controlled by the same slicer.
This worked perfectly for the pivot tables in the same sheet. All the Pivot Tables had same index.
But i also have pivot tables in multiple sheets with same cache index, but the code is not working for the pivot tables in other sheets.
Can you please help me out for connecting pivots in different sheets to a single slicer? I've got codes for syncing filters of all pivots but couldnt find a code for slicer report connections from multiple sheets.
Thanks again for your time :clap:
This one goes through each slicer cache in the workbook, looks at its first pivot table's cache index, then goes through all the other pivot tables in the workbook and if they're based on the same pivot cache, connects the slicer cache to that pivot table:
Code:Sub blah1()
For Each sc In ThisWorkbook.SlicerCaches
If sc.PivotTables.Count > 0 Then
ThisSCIndex = sc.PivotTables(1).CacheIndex
For Each sht In ThisWorkbook.Sheets
For Each pt In sht.PivotTables
If pt.CacheIndex = ThisSCIndex Then sc.PivotTables.AddPivotTable (pt)
Next pt
Next sht
End If
Next sc
End Sub
This one looks at all the slicers currently associated with the pivot table that the selected cell is in, then goes through all the pivot tables in the workbook and if they're the same pivot cache connects the slicer cache to that pivot table.
Code:Sub blah2()
'select a cell on a pivot table first!
Set pt = Selection.PivotTable
For Each slcr In pt.Slicers
For Each sht In ThisWorkbook.Sheets
For Each pvt In sht.PivotTables
If pvt.CacheIndex = pt.CacheIndex Then slcr.SlicerCache.PivotTables.AddPivotTable (pvt)
Next pvt
Next sht
Next slcr
End Sub
This next one needs you to select a slicer first and connects all pivot tables in the workbook which use the same pivot table cache as the slected slicer's pivot table's cache to it - but just that slicer.Code:Sub blah3()
'select a pivot table's slicer first!
Set SLCR = ThisWorkbook.ActiveSlicer
If SLCR Is Nothing Then
MsgBox "Select a slicer!"
Else
Set sc = SLCR.SlicerCache
Set thisPvt = sc.PivotTables(1) 'will fail if it's not linked to any pivot table.
For Each sht In ThisWorkbook.Sheets
For Each pvt In sht.PivotTables
If pvt.CacheIndex = thisPvt.CacheIndex Then sc.PivotTables.AddPivotTable (pvt)
Next pvt
Next sht
End If
End Sub
I tested all the above codes, I did not get any errors, execution was smooth. BUT the pivot tables are still not connected.
Below is my code, Ive created a Slicer for pivottable "10A" & Pivot field "Type" and tried your 3rd code.
Code:Sub Add_Slicer()
'Creating New Slicer(Recorded it)
ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables( _
"10A"), "Type").Slicers.Add ActiveSheet, , "Type", _
"Type", 139.5, 648, 144, 198.75
ActiveSheet.Shapes.Range(Array("Type")).Select 'Selected the slicer
Set SLCR = ThisWorkbook.ActiveSlicer
If SLCR Is Nothing Then
MsgBox "Select a slicer!"
Else
Set sc = SLCR.SlicerCache
Set thisPvt = sc.PivotTables(1) 'will fail if it's not linked to any pivot table.
For Each sht In ThisWorkbook.Sheets
For Each pvt In sht.PivotTables
If pvt.CacheIndex = thisPvt.CacheIndex Then sc.PivotTables.AddPivotTable (pvt)
Next pvt
Next sht
End If
End Sub
How are you determining this?Code:Sub Add_Slicer()
Set SLCR = ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables("10A"), "Type").Slicers.Add(ActiveSheet, , "Type", "Type", 139.5, 648, 144, 198.75)
Set sc = SLCR.SlicerCache
Set thisPvt = sc.PivotTables(1) 'will fail if it's not linked to any pivot table.
For Each sht In ThisWorkbook.Sheets
For Each pvt In sht.PivotTables
If pvt.CacheIndex = thisPvt.CacheIndex Then sc.PivotTables.AddPivotTable (pvt)
Next pvt
Next sht
End Sub
Arethe pivot tables all in the workbook that the code is sitting in?
Yes they are in the same workbook but different sheets. After i run the code and view slicer’s report connections, only the pivot table from which the slicer was created is checked. Rest of the pivot tables are not checked.
(PS: Pivot tables were generated using “show report filter pages” from a master pivot table. Slicer was created from the same master pivot for which im trying to connect its respective pivot tables generated)
In the attached, 2 buttons.
Works here.
Last but not the least. Can you help me out to create a slicer for each of the filter in pivot table instead creating a code for individual filter.
Basically i want to replace the below code with a loop for each of the pivotfield.
Code:Set SLCR = ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables("10A"), "Type") _
.Slicers.Add(ActiveSheet, , "Type", "Type", 139.5, 648, 144, 198.75)
Show report filter pages… only works for items in the Filter area (PageField areas) and then only for the filtered items there (see below for more on this).If you've ticked the check box Select Multiple Items in any of those field's dropdowns new sheets will only be generated for ticked items.Code:Sub blah4()
Set pt = ActiveSheet.PivotTables("10A")
For Each pf In pt.PageFields
pt.ShowPages pf
Next pf
End Sub
Yes I did… I'll be back later.
Do something along these lines:Code:Sub Add_Slicers2()
Set pt = ActiveSheet.PivotTables("10A")
TheTop = 140
For Each pf In pt.PivotFields
Set sc = ActiveWorkbook.SlicerCaches.Add2(pt, pf.Name)
Set SLCR = sc.Slicers.Add(ActiveSheet, , pf.Name, pf.Name, TheTop, 648, 144, 198.75)
TheTop = TheTop + 5
For Each sht In ThisWorkbook.Sheets
For Each pvt In sht.PivotTables
If pvt.CacheIndex = pt.CacheIndex Then sc.PivotTables.AddPivotTable (pvt)
Next pvt
Next sht
Next pf
End Sub