Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Pivottable Slicers > Synchronising Slicers

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 272 times)

Next: Showing Selected Slicer Items On a Worksheet


 


Comments

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

 


Comment by: Marcel (21-6-2019 14:30:00)

I took a good look at your code, adjusted it to work in my sheet and I am impressed. Works great. This is indeed was I was looking for.

However clicking on one slicer and synching the matching other slicer takes up to 27-40 seconds depending on which Region in the slicer was selected.

What could be causing this huge amount of time. I have
- About 9000 records, 83 columns
- 11 slicers
- Only 2 slicers are synched with your script
- Dozens of PT's
- All combined in a dashboard

 


Comment by: Jan Karel Pieterse (21-6-2019 17:08:00)

Hi Marcel,

I'm not sure. But each change in filter will cause an update of all of the attached pivottables, so it'd make sense if it would take a while if there are many pivottables attached to the two slicers.

This could probably be better solved by making sure there is only one slicer to filter on rather than having to synch two!

 


Comment by: Zak (29-8-2019 14:09:00)

Hi Jan,

I have a table with 2 slicers and 2 pivot tables with slicers
The 2 pivot tables along with 3 others(these 3 do not have slicers) count items from 2 columns in the original table and then display as graphs
I want the 2 slicers from the table labelled Region and Country to link to the 2 pivot table slicers of the same name
The pivot table slicers are called Slicer_Region and Slicer_Country
The table slicers are called Slicer_Region1 and Slicer_Country1

How can I modify your code to pull this off

 


Comment by: Jan Karel Pieterse (29-8-2019 15:51:00)

Hi Zak,

I have created a setup where a pivottable has a slicer named Slicer_Shipcountry1 and a table has a slicer named Slicer_Shipcountry2. To have the PT slicer update the table slicer:

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oSl As SlicerItem
    Dim oSc1 As SlicerCache
    Dim oSc2 As SlicerCache
    Dim sSheet1 As String
    Dim sSheet2 As String

    Dim sFirstSlicerItemText As String
    Dim lCt As Long
    Dim bFound As Boolean

    If mbNoEvents Then Exit Sub
    On Error Resume Next    'IN case a slicer item in slicer 1 is not present in slicer 2
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    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
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    mbNoEvents = False
End Sub


Best to hide the table slicer (no code responds to changes on a table slicer) and just copy the pivot table slicer to the sheet where the table is if you need the slicer to also be available from there.

 


Comment by: Zak (29-8-2019 16:18:00)

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)

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)

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)

Hi Philip,

Thanks!

 


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.

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.