Linking filters from one slicer / pivot table, to another

Question

Just a heads up, this is my first time asking a question on a forum. So apologies in advance if I don't follow the etiquette.

I have 3 pivot tables, each with its own data set and each has 3 slicers. The data in the pivot tables has different values and is of a different size. However, there are 3 linking columns with the same/similar identifiers. Project, Package and Discipline are common identifiers across the 3 data sets. There is a slicer for each one.

I am trying to link them through vba. I have written some code that is meant to cycle through each filter in the first slicer, if it's selected then it should select the corresponding filter in the second slicer. I can't quite work out why it's not working. As far as I can tell, its something to do with 'for each item in slicercache'. But beyond that I can't wrap my head around it.

I even tried some simplified code, just to check I understood the basics and that doesn't work either.

Simplified code:

'Sub Test()

Dim sc As SlicerCache
Dim si As SlicerItem

Set sc = ThisWorkbook.SlicerCaches("Slicer_Project")

For Each si In sc.SlicerItems
    If si.Selected = True Then
        si.Selected = False
    Else
        si.Selected = True
    End If
Next si

End Sub

Full code:

Sub Sort_Slicers()
Dim Master_Slice As SlicerCache, Subject_Slice As SlicerCache
Dim Ctr As Integer, Ctr2 As Integer
Dim Sl_Item As SlicerItem
Dim wb As Workbook

TurnStuffOff

Set wb = ThisWorkbook

For Ctr = 1 To 3
    If Ctr = 1 Then
        Set Master_Slice = wb.SlicerCaches("Slicer_Project")
    ElseIf Ctr = 2 Then
        Set Master_Slice = wb.SlicerCaches("Slicer_Package")
    ElseIf Ctr = 3 Then
        Set Master_Slice = wb.SlicerCaches("Slicer_Disc")
    End If

    For Ctr2 = 1 To 2
        If Ctr2 = 1 Then
            If Ctr = 1 Then
                Set Subject_Slice = wb.SlicerCaches("Slicer_Project1")
            ElseIf Ctr = 2 Then
                Set Subject_Slice = wb.SlicerCaches("Slicer_Package1")
            ElseIf Ctr = 3 Then
                Set Subject_Slice = wb.SlicerCaches("Slicer_Disc1")
            End If
        Else
            If Ctr = 1 Then
                Set Subject_Slice = wb.SlicerCaches("Slicer_Project2")
            ElseIf Ctr = 2 Then
                Set Subject_Slice = wb.SlicerCaches("Slicer_Package2")
            ElseIf Ctr = 3 Then
                Set Subject_Slice = wb.SlicerCaches("Slicer_Disc2")
            End If
        End If

        Subject_Slice.ClearAllFilters

        On Error Resume Next
        For Each Sl_Item In Master_Slice.SlicerItems
            If Sl_Item.Selected = True Then
                Subject_Slice.Sl_Item.Selected = True
            Else
                Subject_Slice.Sl_Item.Selected = False
            End If
        Next Sl_Item
        On Error GoTo 0

    Next Ctr2
Next Ctr

TurnStuffOn
End Sub

On the main code, no errors are thrown, just the objective is not achieved.On the simplified version I get 'Run time error 1004 Application-defined or object-defined error' — JakeBrowno
Have you had a look at my post about this here:jkp-ads.com/Articles/slicers04.asp — jkpieterse
Cross-posted here: mrexcel.com/board/threads/cycling-through-slicer-items.1120584 — jkpieterse
@jkpieterse having a quick skim through, it seems as though mine should work following that. Unless I am missing something. My sheet is set up like so: Only one of the three slicers is visible to the end user, the other 2 are hidden in other sheets. So my hope is that I can set it so they only need to interface with the slicer for one of the 3 pivots, but effect all the data. — JakeBrowno

JakeBrowno JakeBrowno · Accepted Answer · 2020-02-06T09:15:18

Just to let everyone know, I got to the bottom of it.

There was nothing wrong with the code at all, it was the fact that the first pivot table was loaded from a data table. I deleted the pivot, reset it so it read straight from the excel file and it worked fine.

Linking filters from one slicer / pivot table, to another

1 Answers