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

Next: Showing Selected Slicer Items On a Worksheet


 


Comments

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

 


Comment by: Richard (4/4/2017 4:45:48 PM)

The event Workbook_SheetPivotTableUpdate(...) doesn't get called for me when I click on a slicer item. I am using Excel 2010, is that the reason?

 


Comment by: Jan Karel Pieterse (4/4/2017 4:48:47 PM)

Hi Richard,

No, that event should work. Have you placed it in the ThisWorkbook module?

 


Comment by: Richard (4/5/2017 10:32:21 AM)

Hi

I did try moving it to ThisWorkbook, but it made no difference.

After reading a copious number of blogs on the subject I eventually changed the name to Worksheet_PivotTableUpdate(...) and, hey presto, it work.

The problem then was that I had it calling a function to refresh a Pivot Table, so it got recursively called. Another trail round the blogs and found [B]Application.EnableEvents = True/False[/B] which solved that problem.

So I have now got Slicer2 dynamically showing the items selected by clicking on Slicer1.

 


Comment by: Jan Karel Pieterse (4/5/2017 10:54:46 AM)

Hi Richard,

That is very odd, both on my Excel 2010 and 2016 it is called as written here on this page: Workbook_SheetPivotTableUpdate

 


Comment by: Michael Easterbrook (7/4/2017 7:39:06 PM)

What if your slicer has 50,000 items? It would take a very long time to loop through every item.

 


Comment by: Jan Karel Pieterse (7/5/2017 11:12:49 AM)

Hi Michael,

Yes, that would make this quite slow unfortunately. Especially because the pivottables will refresh at each change in the loop.

 


Comment by: Abdul (10/3/2017 11:44:29 AM)

How can I loop through slicer items.

when I loop it keep on working with all items which are not in the slicer?

 


Comment by: Jan Karel Pieterse (10/3/2017 4:53:12 PM)

Hi Abdul,

Please show me the relevant piece of code?

 


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 but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

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