How to use VBA Slicer to connect two PivotTable

[wendyto]

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!

15728

[p45cal]

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:

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

[wendyto]

It works! Thank you very much for your teaching!

[predzer]

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


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"))

[p45cal]

Hi, the above code works for only 2 pivot tables.It works for 3 here:
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
Even your code works if the pivot tables concerned all use the same PivotCache.

[predzer]

It works for 3 here:
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
Even your code works if the pivot tables concerned all use the same PivotCache.


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


SC.PivotTables.AddPivotTable (PT3)

this


SC.PivotTables.AddPivotTable (10A)

then the code wont run. so im looking code for only connecting existing pivot tables to a new slicer.

[p45cal]

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:
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
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.
Make sure that the cache index is the same for every pivot table you want to be controlled by the same slicer.

[predzer]

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:
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
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.
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:

[p45cal]

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:

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.

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.
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

[predzer]

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.
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.


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

[p45cal]

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



BUT the pivot tables are still not connected.
How are you determining this?
Arethe pivot tables all in the workbook that the code is sitting in?

[predzer]

How are you determining this?
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)

[p45cal]

In the attached, 2 buttons.
Works here.

[predzer]

In the attached, 2 buttons.
Works here.

Im so so greatfull to you. The above code was not running from that respective workbook so i put in personal book and changed thisworkbook to activeworkbook and it did the magic!!! :bow:

[predzer]

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.


Set SLCR = ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables("10A"), "Type") _
.Slicers.Add(ActiveSheet, , "Type", "Type", 139.5, 648, 144, 198.75)

[p45cal]

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).
Sub blah4()
Set pt = ActiveSheet.PivotTables("10A")
For Each pf In pt.PageFields
pt.ShowPages pf
Next pf
End Sub

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.

[predzer]

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).
Sub blah4()
Set pt = ActiveSheet.PivotTables("10A")
For Each pf In pt.PageFields
pt.ShowPages pf
Next pf
End Sub

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.


This is fine. I think you misunderstood my post.

I wanted to create a new slicer for each of the PageField areas.

[p45cal]

Yes I did… I'll be back later.

[p45cal]

Do something along these lines:
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

[predzer]

Do something along these lines:
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


The above code runs smooth, but it creates a new slicer for every column in the data sheet.

Say i have 30 columns and selected only 5 filters for a pivot table. The above code is creating 30 slicers. I only need 5 slicers for that pivot fields.

[p45cal]

Try changing
For Each pf In pt.PivotFields
to
For Each pf In pt.RowFields
or
For Each pf In pt.PageFields
or
For Each pf In pt.ColumnFields

[predzer]

Try changing
For Each pf In pt.PivotFields
to
For Each pf In pt.RowFields
or
For Each pf In pt.PageFields
or
For Each pf In pt.ColumnFields

Awesome! Thanks a lot :clap:

[predzer]

Try changing
For Each pf In pt.PivotFields
to
For Each pf In pt.RowFields
or
For Each pf In pt.PageFields
or
For Each pf In pt.ColumnFields

I got a new issue. now i want to select all the slicers and move to a hidden sheet. I got codes in place to create a new sheet and hide it.

I just want code now to select all the slicers in the activesheet. I also have couple of charts in the same sheet, i dont want to select the charts. i used select all shapes and even charts got selected. so i am looking for something like below.



Sheets.add After:=ActiveSheet
ActiveSheet.Name = "Hidden"
Sheets("Master").Select

' I need code here to select all the slicers in the master sheet.

Selection.ShapeRange.Group.Select
Selection.Cut
Sheets("Hidden").Select
ActiveSheet.Paste
ActiveWindow.SelectedSheets.Visible = False


Thanks for your time :yes

[p45cal]

Put them there from the outset:
Set SLCR = sc.Slicers.Add(ActiveSheet, , pf.Name, pf.Name, TheTop, 648, 144, 198.75)
change the red.

[predzer]

Put them there from the outset:
Set SLCR = sc.Slicers.Add(ActiveSheet, , pf.Name, pf.Name, TheTop, 648, 144, 198.75)
change the red.

Thank You p45cal :yes

[predzer]

I could'nt find the respective thread, so posting here since its relevant to this.

I need help on modifying show report filter page code.

I have two pivot tables in the master sheet say pivot table 10A & 10B with different row / column & value field but pagefield is same.

1) Now i need to create a new worksheet for every pivot item of pivot field "Building" of pivot table 10A which i can get using show report filter page. Say the worksheets are now named Building 1 , Building 2, Building 3, Building 4.

2) Now, From pivot table 10 B, i need to create pivot table for every pivot item of pivot field "Building". if i use show report filter page the new sheets are created and named Building 1(1) , Building 2(1), Building 3(1), Building 4(1)

What i am looking at is when i am generating sheets from pivot table 10B, i need new pivot tables to be placed in its respective existing sheet (Building 1 , Building 2, Building 3, Building 4) and in range D1.

[p45cal]

This one is difficult to provide a robust solution to.
The Show report filter pages command makes multiple new sheets when the command is executed. There is no control over where the pivot tables go, new sheets are always created.
Two ways you might go:
1. Exceute the Show report filter pages for each pivot table, then go hunting for the newly created sheets and try to match up the similar names then move pivot tables around, then delete redundant sheets.
2. Instead of using the Show report filter pages command, try to simulate what it does by copying the pivot table according to what's selected in the page field(s), and set up the copied pivots in the same way as the Show report filter pages command would have. You do have control over where the copied pivots go.

Either way, a considerable amount of coding is required.
I suggest posting a brand new thread for this question.

[predzer]

This one is difficult to provide a robust solution to.
The Show report filter pages command makes multiple new sheets when the command is executed. There is no control over where the pivot tables go, new sheets are always created.
Two ways you might go:
1. Exceute the Show report filter pages for each pivot table, then go hunting for the newly created sheets and try to match up the similar names then move pivot tables around, then delete redundant sheets.
2. Instead of using the Show report filter pages command, try to simulate what it does by copying the pivot table according to what's selected in the page field(s), and set up the copied pivots in the same way as the Show report filter pages command would have. You do have control over where the copied pivots go.

Either way, a considerable amount of coding is required.
I suggest posting a brand new thread for this question.

Thanks p45cal. But I some how figured out for this, Have posted the code below, will be helpful for others in future



Sub Filter_Pages()

Dim i As Integer
Dim sItem As String
Dim pivotSht As Worksheet
Dim ws As Worksheet
Dim PT1 As PivotTable
Dim PT2 As PivotTable

Set pivotSht = Sheets("Master")
Application.ScreenUpdating = False

Set PT1 = pivotSht.PivotTables("10A")
Set PT2 = pivotSht.PivotTables("10B")

PT1.ShowPages PageField:="Building"
pivotSht.move Before:=Worksheets(1)

With PT2

With .PivotFields("Building")
.PivotItems(1).Visible = True '---hide all items except item 1
For i = 2 To .PivotItems.Count
.PivotItems(i).Visible = False
Next
For i = 1 To .PivotItems.Count
.PivotItems(i).Visible = True

If i <> 1 Then .PivotItems(i - 1).Visible = False
sItem = .PivotItems(i)

With PT2.PivotFields("Building")
.PivotItems(i).Visible = True
EnableMultiplePageItems = False
With PT2.TableRange2.Copy

Sheets(sItem).Activate
With ActiveSheet
Range("D1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End With
End With
End With
Next i
End With
End With
End Sub

[Paul_Hossler]

Try




For Each pf In pt.PageFields