Sync Slicers in Excel

July 05, 2017 - by Bill Jelen

How can you have a slicer drive two pivot tables if they came from different data sets?

I recorded this video showing how to add a few lines of VBA code to your workbook to do this:

For popular questions, I am posting the code here.

FAQ #1

Can you type the code for me because I am unable to type?

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

    ' These names come from Slicer Settings dialog box
    Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Name")
    Set sc2 = ThisWorkbook.SlicerCaches("Slicer_Name1")
    Set sc3 = ThisWorkbook.SlicerCaches("Slicer_Region2")
    Set sc4 = ThisWorkbook.SlicerCaches("Slicer_Region1")

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    sc2.ClearManualFilter
    sc4.ClearManualFilter

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

    For Each SI3 In sc3.SlicerItems
            sc4.SlicerItems(SI3.Name).Selected = SI3.Selected
    Next SI3

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