VBA Code to Sync Slicers

Langtn02

New Member
Joined
Jan 19, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I'm trying to sync multiple slicers to connect pivot tables from multiple sources. I'm new to VBA and tried the below code which isn't working. Can anyone help?

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 SC4 As SlicerCache
Dim SC5 As SlicerCache
Dim SC6 As SlicerCache
Dim SI2 As SlicerItem
Dim SC7 As SlicerCache
Dim SC8 As SlicerCache
Dim SC9 As SlicerCache
Dim SI3 As SlicerItem
Dim SC10 As SlicerCache
Dim SC11 As SlicerCache
Dim SC12 As SlicerCache
Dim SI4 As SlicerItem
Dim SC13 As SlicerCache
Dim SC14 As SlicerCache
Dim SC15 As SlicerCache
Dim SI5 As SlicerItem

' These names come from Slicer Setting dialog box
Set SC1 = ThisWorkbook.SlicerCaches("Slicer_Management_Level_1")
Set SC2 = ThisWorkbook.SlicerCaches("Slicer_Management_Level_2")
Set SC3 = ThisWorkbook.SlicerCaches("Slicer_Management_Level_3")
Set SC4 = ThisWorkbook.SlicerCaches("Slicer_Management_Level_4")
Set SC5 = ThisWorkbook.SlicerCaches("Slicer_Management_Level_5")
Set SC6 = ThisWorkbook.SlicerCaches("Slicer_Management_Level_11")
Set SC7 = ThisWorkbook.SlicerCaches("Slicer_Management_Level_21")
Set SC8 = ThisWorkbook.SlicerCaches("Slicer_Management_Level_31")
Set SC9 = ThisWorkbook.SlicerCaches("Slicer_Management_Level_41")
Set SC10 = ThisWorkbook.SlicerCaches("Slicer_Management_Level_51")
Set SC11 = ThisWorkbook.SlicerCaches("Slicer_Management_Level_12")
Set SC12 = ThisWorkbook.SlicerCaches("Slicer_Management_Level_22")
Set SC13 = ThisWorkbook.SlicerCaches("Slicer_Management_Level_32")
Set SC14 = ThisWorkbook.SlicerCaches("Slicer_Management_Level_42")
Set SC15 = ThisWorkbook.SlicerCaches("Slicer_Management_Level_52")

Application.ScreenUpdating = False
Application.EnableEvents = False

SC6.ClearManualFilter
SC7.ClearManualFilter
SC8.ClearManualFilter
SC9.ClearManualFilter
SC10.ClearManualFilter
SC11.ClearManualFilter
SC12.ClearManualFilter
SC13.ClearManualFilter
SC14.ClearManualFilter
SC15.ClearManualFilter

For Each SI1 In SC1.SlicerItems
SC6.SlicerItems(SI1.Name).Selected = SI1.Selected
Next SI1

For Each SI1 In SC1.SlicerItems
SC10.SlicerItems(SI1.Name).Selected = SI1.Selected
Next SI1

For Each SI2 In SC2.SlicerItems
SC7.SlicerItems(SI2.Name).Selected = SI2.Selected
Next SI2

For Each SI2 In SC2.SlicerItems
SC12.SlicerItems(SI2.Name).Selected = SI2.Selected
Next SI2

For Each SI3 In SC3.SlicerItems
SC8.SlicerItems(SI3.Name).Selected = SI3.Selected
Next SI3

For Each SI3 In SC3.SlicerItems
SC13.SlicerItems(SI3.Name).Selected = SI3.Selected
Next SI3

For Each SI4 In SC4.SlicerItems
SC9.SlicerItems(SI4.Name).Selected = SI4.Selected
Next SI4

For Each SI4 In SC4.SlicerItems
SC14.SlicerItems(SI4.Name).Selected = S14.Selected
Next SI4

For Each SI5 In SC5.SlicerItems
SC10.SlicerItems(SI5.Name).Selected = SI5.Selected
Next SI5

For Each SI5 In SC5.SlicerItems
SC15.SlicerItems(SI5.Name).Selected = SI5.Selected
Next SI5

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


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top