Synchronising Slicers

Oftentimes a reporting workbook contains more than one pivottable. As I showed you already, synchronising pivottables that use the same pivotcache is very easy if you tie them together with one or more slicers pointing to both pivottables. You use the "Pivottable Connections" button on the ribbon for that.

However, it frequently happens that pivottables do not share a pivotcache, but still need to be in sync, for instance if your slicer is filtering on a reporting period such as Year, Quarter or Month. In the code below I assume all pivotcaches share the fieldnames to filter on. I also assume the tied-together slicercaches share the same values, in other words, all years appearing in Slicer_Year also appear in Slicer_Year1 and vice versa. If your situation differs (values in slicercache 1 might be missing in slicercache 2 or the other way around), you will need to take special precautions.

Setting things up

The first action needed is to make sure each slicer is connected to all relevant pivottables belonging to its pivotcache. Take care NOT to add a pivottable to a slicer for which the field in question is not used in that pivottable, because a slicer can operate on a field that isn't included in the pivottable itself. It will filter your pivottable and the pivot itself gives no clue that it was filtered (other than the slicer).

Suppose you have three reports: Monthly, Quartely and Yearly. You might have three slicers for those pivottables: Year, Month and Quarter. But for the quarterly and yearly pivottable, it makes no sense to tie them to the Month slicer. In fact, it would turn them all into a monthly report. In a situation like this, only the Year slicer is a sensible one to connect to all three pivottables. If you would want to connect the Quarter slicer to the Month slicer, you would need some VBA which, after selecting a month, sets the associated Quarter in the Quarter slicer. The code below isn't that smart, it just syncs Year, Quarter and Month slicers, assuming you did all of the slicers pivottable connections correctly.

Triggering a change in a slicer filter

There is no event tied to slicer actions directly. However, clicking a slicer triggers the Workbook_SheetPivotTableUpdate event in the ThisWorkbook module (there is a similar event in the sheet modules, but I want one generic event which responds to any pivottable update). The empty event stub looks like this:

PrivateSub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)

End Sub

Sh is an object variable pointing to the worksheet on which a pivottable just updated. Target is an object variable pointing to the pivottable that just updated.

The first thing the code needs to do is find out which slicers are connected to the pivottable that just changed and set a pointer to the ones we want to use to sync the other pivottables to.

First, we declare some variables:

    Dim oScMonth As SlicerCache
    Dim oScKwrt As SlicerCache
    Dim oScYear As SlicerCache
    Dim oSc As SlicerCache
    Dim oPT As PivotTable
    Dim oSi As SlicerItem

The first three are going to hold the Year, Quarter and Month slicers tied to the changed pivottable. Note that no more than two of these three are in fact tied to the pivottable: either Year and Month, Year and Quarter or just Year.

Finding the slicer that was clicked

So now step through all SlicerCaches and then through all their associated pivottables to find out which slicers are tied to the changed pivottable:

    For Each oSc In ThisWorkbook.SlicerCaches
        For Each oPT In oSc.PivotTables
            If oPT.Name = Target.Name And oPT.Parent.Name = Target.Parent.Name Then
                If oSc.Name Like "*Year*" Then
                    Set oScYear = oSc
                ElseIf oSc.Name Like "*Month*" Then
                    Set oScMonth = oSc
                ElseIf oSc.Name Like "*Quarter*" Then
                    Set oScKwrt = oSc
                End If
                Exit For
            End If
        Next
        If Not oScYear Is Nothing And Not oScMonth Is Nothing And Not oScKwrt Is Nothing Then Exit For
    Next

As you can see, we only pick up the slicers Month, Quarter and Year because those are the ones we want to sync.

Synchronising the slicers

Now that we have the slicer(s) that have been clicked, we want to sync them with slicers with similar names. By Default, Excel names SlicerCaches like this: Slicer_FieldName. If another slicer with the same fieldname is added (which adds a SlicerCache too), it is called Slicer_FieldName1, the next one is called Slicer_FieldName2 and etcetera. So what the code below does is loop through all slicercaches again, looking at their names to figure out which ones belong together.

The code has a special trick to avoid problems. Suppose you have a slicer which has its first item selected and the remaining items de-selected. Suppose that the sister slicer is clicked and we click the second item. When the code runs through all slicer items, it will start with the first one, which will be de-selected. This causes the slicer to select all items, because it is not allowed to de-select all items of a slicer. Subsequently the code runs through the remaining items and sets them as needed. The end result is that both item 1 and item 2 are selected, rather than just item 2. The trick around this is setting the last slicer item to "Selected" before running through the loop:

    If Not oScYear Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScYear.Name, 7, 3) And oSc.Name <> oScYear.Name Then
                'This one has a similar fieldname (first three characters are compared in this case)
                'but not the same name, as that would be the same slicercache.
                'So synch it with the changed year slicer
                'If a slicer has the very first item selected and you subsequently de-select it,
                'the end result is that all sliceritems get selected. So select the last item of the slicer first
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScYear.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If

Putting it all together

So if you put all of this together, this is what you end up with:

'Variable to prevent event looping:
Dim mbNoEvent As Boolean

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oScMonth As SlicerCache
    Dim oScKwrt As SlicerCache
    Dim oScYear As SlicerCache
    Dim oSc As SlicerCache
    Dim oPT As PivotTable
    Dim oSi As SlicerItem
    Dim sYear As String
    Dim bUpdate As Boolean
    'Prevent event looping, changing a slicer in this routine also triggers this routine
    If mbNoEvent Then Exit Sub
    mbNoEvent = True
    bUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    For Each oSc In ThisWorkbook.SlicerCaches
        For Each oPT In oSc.PivotTables
            If oPT.Name = Target.Name And oPT.Parent.Name = Target.Parent.Name Then
                If oSc.Name Like "*Year*" Then
                    Set oScYear = oSc
                ElseIf oSc.Name Like "*Month*" Then
                    Set oScMonth = oSc
                ElseIf oSc.Name Like "*Quarter*" Then
                    Set oScKwrt = oSc
                End If
                Exit For
            End If
        Next
        If Not oScYear Is Nothing And Not oScMonth Is Nothing And Not oScKwrt Is Nothing Then Exit For
    Next
    If Not oScYear Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScYear.Name, 7, 3) And oSc.Name <> oScYear.Name Then
                'This one has a similar fieldname (first three characters are compared in this case)
                'but not the same name, as that would be the same slicercache.
                'So synch it with the changed year slicer
                'If a slicer has the very first item selected and you subsequently de-select it,
                'the end result is that all sliceritems get selected. So select the last item of the slicer first
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScYear.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If
    If Not oScKwrt Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScKwrt.Name, 7, 3) And oSc.Name <> oScKwrt.Name Then
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScKwrt.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If
    If Not oScMonth Is Nothing Then
        For Each oSc In ThisWorkbook.SlicerCaches
            If Mid(oSc.Name, 7, 3) = Mid(oScMonth.Name, 7, 3) And oSc.Name <> oScMonth.Name Then
                oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
                For Each oSi In oScMonth.SlicerItems
                    On Error Resume Next
                    If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
                        oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
                    End If
                Next
            End If
        Next
    End If
    mbNoEvent = False
    Application.ScreenUpdating = bUpdate
End Sub

Sample files

Download sample Excel workbooks (29 Aug 2019, downloaded 1.865 times)

Next: Showing Selected Slicer Items On a Worksheet

Comments

Showing last 8 comments of 90 in total (Show All Comments):

Comment by: Zak (29-8-2019 16:18:00) deeplink to this comment

Hi Jan,

I assume if there are multiple slicers on each table type, it would just be more variables and the following code repeated with the new variables, oSc3, oSc4, oSc5, etc

    Set oSc1 = SlicerCaches("Slicer_Shipcountry1")
    Set oSc2 = SlicerCaches("Slicer_Shipcountry2")
    mbNoEvents = True
    With oSc2
        sFirstSlicerItemText = .SlicerItems(1).Name
        .SlicerItems(1).Selected = True
        For lCt = 2 To oSc2.SlicerItems.Count
            .SlicerItems(lCt).Selected = False
        Next
    End With
    With oSc1
        For Each oSl In .SlicerItems
            If oSl.Name = sFirstSlicerItemText Then
                bFound = True
            End If
            oSc2.SlicerItems(oSl.Caption).Selected = oSl.Selected
        Next
    End With
    If bFound = False Then
        'First slicer item was not in second slicer
        oSc2.SlicerItems(1).Selected = False
    Else
        oSc2.SlicerItems(1).Selected = oSc1.SlicerItems(1).Selected
    End If

Comment by: Jan Karel Pieterse (29-8-2019 16:43:00) deeplink to this comment

Hi Zak,

Yes that would work. Alternatively you could move the code out of the Pivot event into a normal module, adding the slicernames as arguments. Then you could pass the slicernames as arguments to the new sub.

So the heading of the new sub would look like:

Sub Synch2Slicers(oSc1 As SlicerCache, oSc2 As SlicerCache)
    Dim oSl As SlicerItem
    Dim sFirstSlicerItemText As String
    Dim lCt As Long
    Dim bFound As Boolean

    On Error Resume Next    'In case a slicer item in slicer 1 is not present in slicer 2

    With oSc2
        sFirstSlicerItemText = .SlicerItems(1).Name
        .SlicerItems(1).Selected = True
        For lCt = 2 To oSc2.SlicerItems.Count
            .SlicerItems(lCt).Selected = False
        Next
    End With
    With oSc1
        For Each oSl In .SlicerItems
            If oSl.Name = sFirstSlicerItemText Then
                bFound = True
            End If
            oSc2.SlicerItems(oSl.Caption).Selected = oSl.Selected
        Next
    End With
    If bFound = False Then
        'First slicer item was not in second slicer
        oSc2.SlicerItems(1).Selected = False
    Else
        oSc2.SlicerItems(1).Selected = oSc1.SlicerItems(1).Selected
    End If

End Sub

ThisWorkbook would then change to a simpler:

Option Explicit

Dim mbNoEvents As Boolean

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    If mbNoEvents Then Exit Sub
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Synch2Slicers SlicerCaches("Slicer_Shipcountry1"), SlicerCaches("Slicer_Shipcountry2")
    'Add more of those above if needed, just make sure you pair the right slicer names in the correct order
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    mbNoEvents = False
End Sub

Comment by: Philip (25-11-2019 11:08:00) deeplink to this comment

Hi Jan,

Thank you for all your solutions to complex problems. I have a worksheet setup to hold all slicers in one place which update quite a lot of number pivot tables. I had the same problem with performance. I use a routine on the pivot cache with the master slicers to turn off/on ManualUpdate on all pivot tables in the workbook. Performance is acceptable now. I also show the user that the report is being updated with a form.

Sub PivotTableManualUpdate(bSwitch As Boolean)

    Dim oPt As PivotTable
    Dim oWb As Workbook
    Dim oWs As Worksheet

    Set oWb = ActiveWorkbook
    For Each oWs In oWb.Worksheets
        For Each oPt In oWs.PivotTables
            oPt.ManualUpdate = bSwitch
        Next oPt
    Next oWs

End Sub

Hope this can help anyeone with performance problems.

Comment by: Jan Karel Pieterse (25-11-2019 12:04:00) deeplink to this comment

Hi Philip,

Thanks!

Comment by: Axel (12-8-2020 08:34:00) deeplink to this comment

Beste Jan,

Ik heb een Performance Dashboard gemaakt waarbij ik meerdere draaitabellen heb gekoppeld aan 1 slicer. Nu heb ik gisteren een extra draaitabel gemaakt, welke ik ook aan dezelfde slicer wil koppelen. Echter komt deze 'draaitabel 7' niet bij de rapportverbindingen te staan in de slicer. Weet jij hoe ik deze draaitabel toch kan koppelen deze ene slicer?

Met vriendelijke groet,

Axel Tichelaar

Comment by: Jan Karel Pieterse (12-8-2020 14:16:00) deeplink to this comment

Hallo Alex,

Daarvoor dient de draaitabel dezelfde draaitabelcache te gebruiken. Als dat niet het geval is, zie dan de volgende pagina van dit artikel.

Comment by: Don (21-1-2021 18:52:00) deeplink to this comment

Hello Jan,

I have 2 Queries (Ole DB) that I use to create 1 pivot table each (PivotTable1 & PivotTable2) on the same worksheet (ptForecast), with each pivot table having 1 slicer for 'Department' (Slicer_Department and Slicer_Dept), so it has 2 separate pivot caches. The items in the 2 slicers for Department/Dept have the same list of items.

I would like to control both PivotTables with one slicer.

I have tried numerous variations of the code you initially provided and others have asked about in the comments, but so far haven't been able to come up with a solution that updates Slicer_Dept with Slicer_Department's item.

Thanks,
Don

Comment by: Jan Karel Pieterse (22-1-2021 18:12:00) deeplink to this comment

Hi Don,

If you like you can email your attempt to me and I'll see if I can fix the code for you, OK?

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.