connect 3 slicers

[Falkie]

Good afternoon.

I have a question regarding the Learn Excel - Sync Slicers from Different Data Sets video. I am trying to figure out how to get this working with 3 slicers.

The slicer that I am trying to use as the operating slicer is called Slicer_Week. The other two are called Slicer_Week2 and Slicer_Week 3.

When I try to run this macro I get the errorcode 438 and points to the following line: sc2.SlicerItems(SI1.Week2).Selected = SI1.Selected

I have tried many things to get it working but without succes. Can anyone help me? Thanks in advance

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim sc1 As SlicerCache
    Dim sc2 As SlicerCache
    Dim sc3 As SlicerCache
    Dim SI1 As SlicerItem
    Dim SI2 As SlicerItem
   
 
    ' These names come from Slicer Settings dialog box
    Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Week")
    Set sc2 = ThisWorkbook.SlicerCaches("Slicer_Week2")
    Set sc3 = ThisWorkbook.SlicerCaches("Slicer_Week3")
 
 
    Application.ScreenUpdating = False
    Application.EnableEvents = False
 
    sc2.ClearManualFilter
    sc3.ClearManualFilter
   
 
    For Each SI1 In sc1.SlicerItems
            sc2.SlicerItems(SI1.Week2).Selected = SI1.Selected
           
       Next SI1
 
    For Each SI2 In sc1.SlicerItems
            sc3.SlicerItems(SI2.Week3).Selected = SI2.Selected
           
       Next SI2
      
 
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[RoryA]

Welcome to the forum.

What is SI1.Week2 supposed to mean? A SlicerItem doesn't have a Week2 property. I suspect you meant SI1.Name instead.

 

[Falkie]

Welcome to the forum.

What is SI1.Week2 supposed to mean? A SlicerItem doesn't have a Week2 property. I suspect you meant SI1.Name instead.

Thank you for your quick reaction. Is it always supposed to be SI1.Name or does it depend on the name of the slicer?

 

[RoryA]

It should always be SI1.Name which returns the name of the item in the slicer, not the name of the slicer itself.

 

[Falkie]

It should always be SI1.Name which returns the name of the item in the slicer, not the name of the slicer itself.

Thank you! It works fine now!

 

[RoryA]

Glad to help.