Hi,
I have 2 pivot tables from 2 different data sources...I am trying to synchronize multiple slicers to control both pivot tables.
I used below code from "Chiriro" for single slicer to control two pivot tables. My challenge right now is how can i modify the code so that more than one slicer controls both pivot tables with different data source.
I have 2 pivot tables from 2 different data sources...I am trying to synchronize multiple slicers to control both pivot tables.
I used below code from "Chiriro" for single slicer to control two pivot tables. My challenge right now is how can i modify the code so that more than one slicer controls both pivot tables with different data source.
Code:
PrivateSub Workbook_SheetPivotTableChangeSync(ByVal Sh AsObject, ByVal Target As PivotTable)
Const MainSlicer AsString = "Slicer_Brand"
Const Slicer1 AsString = "Slicer_Brand1"
Dim pvt As PivotTable
Dim ws As Worksheet
ForEach ws In ThisWorkbook.Worksheets
ForEach pvt In ws.PivotTables
pvt.ManualUpdate = True
Next pvt
Next ws
If Target.Name = "PivotTable1"Then
Application.ScreenUpdating = False
Application.EnableEvents = False
OnErrorGoTo Catch
Dim slMainSlicer As Slicer
Dim slMainSlicercache As SlicerCache
Dim slItem As SlicerItem
Set slMainSlicercache = ThisWorkbook.SlicerCaches(MainSlicer)
ActiveWorkbook.SlicerCaches(Slicer1).ClearManualFilter
ForEach slItem In ThisWorkbook.SlicerCaches(Slicer1).SlicerItems
If slMainSlicercache.SlicerItems(slItem.Name).Selected Then
slItem.Selected = True
Else
slItem.Selected = False
EndIf
Next
EndIf
Catch:
Application.EnableEvents = True
Application.ScreenUpdating = True
ForEach ws In ThisWorkbook.Worksheets
ForEach pvt In ws.PivotTables
pvt.ManualUpdate = False
Next pvt
Next ws
EndSub