How to use VBA Slicer to connect two PivotTable

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!

Quote:

---

Originally Posted by wendyto

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

---

Quote:

---

Originally Posted by predzer

Hi, the above code works for only 2 pivot tables.

---

It works for 3 here:

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

---

Even your code works if the pivot tables concerned all use the same PivotCache.

Quote:

---

Originally Posted by p45cal

It works for 3 here:

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

---

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

Code:

---

SC.PivotTables.AddPivotTable (PT3)

---

this

Code:

---

SC.PivotTables.AddPivotTable (10A)

---

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

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:

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

---

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.

Quote:

---

Originally Posted by 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:

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

---

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:

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

---

Quote:

---

Originally Posted by p45cal

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

---

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

---

Quote:

---

Originally Posted by predzer

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?

Quote:

---

Originally Posted by p45cal

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)

In the attached, 2 buttons.
Works here.

Quote:

---

Originally Posted by p45cal

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:

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

Code:

---

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.

Quote:

---

Originally Posted by 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).

Code:

---

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.

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

---

Quote:

---

Originally Posted by p45cal

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

---

---

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.

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

Quote:

---

Originally Posted by 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

---

Awesome! Thanks a lot :clap:

Quote:

---

Originally Posted by 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

---

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.

Code:

---

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

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

Quote:

---

Originally Posted by 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.

---

Thank You p45cal :yes

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.

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.

Quote:

---

Originally Posted by 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.

---

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

Code:

---

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

---

Try

Code:

---

For Each pf In pt.PageFields

---