Sync Slicers in Excel

[Bram1212]

Hi!

I tried to use the sync slicers guide (https://www.mrexcel.com/news/sync-slicers-in-excel/) but it's not working for me.

This is the code I have:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim sc1 As SlicerCache
    Dim sc2 As SlicerCache
    Dim SI1 As SlicerItem


    ' These names come from Slicer Settings dialog box
    Set sc1 = ThisWorkbook.SlicerCaches("Slicer_ISO_Year____Week____Day")
    Set sc2 = ThisWorkbook.SlicerCaches("Slicer_ISO_Year____Week____Day1")


    Application.ScreenUpdating = False
    Application.EnableEvents = False


    sc2.ClearManualFilter


    For Each SI1 In sc1.SlicerItems
            sc2.SlicerItems(SI1.Name).Selected = SI1.Selected
    Next SI1


    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

The 2 slicers are on worksheet 1. The pivottables are on 6 other worksheets. So:
Worksheet 1: 2 slicers
Worksheet 2: 1 pivottable connected to slicer 1
Worksheet 3: 1 pivottable connected to slicer 1
Worksheet 4: 1 pivottable connected to slicer 1
Worksheet 5: 1 pivottable connected to slicer 2
Worksheet 6: 1 pivottable connected to slicer 2
Worksheet 7: 1 pivottable connected to slicer 2

Is that something I need to specify in the code?

Thanks!

 

[RoryA]

Welcome to the forum.

What exactly does "doesn't work" mean?

Did you put the code in the worksheet code module of a worksheet with a pivot table on it?

 

[Bram1212]

He Rory,

Thanks!

No the code is on a worksheet without pivottables. The macro does not give an error but does not sync the slicers either.

 

[RoryA]

If there isn't a pivot table on that worksheet, the code won't be triggered. It needs to be in a sheet with a pivot table.

 

[Bram1212]

Do all connected pivottables need to be on the same sheet? Or just one?

No work-around possible?

 

[RoryA]

There just needs to be at least one of them on the sheet with the code.

 

[Bram1212]

I have pasted all pivot tables in the same worksheet, no luck.

Any other suggestions?

 

[RoryA]

Is the code behind that same worksheet?

 

[Bram1212]

Yes it is.

 

[RoryA]

Add this:

Msgbox "Triggered"

before the Set sc1 = ... line and then change a slicer. Do you see a message?