Cycling through slicer items

[jakebrowno]

Just a heads up, this is my first time asking a question on a MrExcel 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

 

[jkpieterse]

Cross-posted here: Linking filters from one slicer / pivot table, to another

 

[jakebrowno]

Cross-posted here: Linking filters from one slicer / pivot table, to another

Indeed, is that generally against etiquette? I just figures the more places you ask, the better/quicker the answers.

 

[jkpieterse]

People do this voluntarily, don't waste the volunteer's time. I'm not against cross posting, but at the very least let us know.

 

[jakebrowno]

People do this voluntarily, don't waste the volunteer's time. I'm not against cross posting, but at the very least let us know.

As said in the original post, very much my first time posting - so was unaware of that. Will remember it for the future.

 

[jakebrowno]

Bump - still very stuck on this one

 

[jakebrowno]

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 reading straight from the excel file and it worked fine.