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

All comments about this page:


Comment by: Jon Acampora (3/13/2014 8:39:20 PM)

Great technique, thanks for sharing! It's probably obvious to some, but you might want to mention that you will need to hide the additional slicers or move them out of view.

The Selection Pane can be used to Show/Hide the duplicate slicers, and it is also a good place to see a listing of all the slicer names.

 


Comment by: Jeff Weir (3/19/2014 3:01:26 AM)

Hi Jan Karel. Nice work.

I wrote some code over at the Contextures blog some time back for syncing pivots that set up a temp slicer to sync them if they shared the same cache, and otherwise used a Dictionary to store just the visible items of the ‘master’ field in that dictionary, so that those settings could be applied again and again to the various ‘slave’ pivots on other caches.

Using a Dictionary approach on pivots with different caches was very fast in the case that you had thousands of PivotItems in your PivotFields and multiple pivots to sync, as you only had to iterate through the master field the one time to record just the visible items, and then could clear the slave field and then just hide any items in the slave that were NOT in the dictionary.

Code is at http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/ if you’re interested . Or a dictionary-only approach at http://blog.contextures.com/archives/2012/08/28/update-specific-pivot-tables-automatically/

That code’s due for a refresh, as the bit that set up slicers (for pivots on the same cache) errors out if the user has already set up a slicer. Plus it strikes me that I can make it even faster if say I’ve got 3 pivots that share cache A and 3 pivots that share cache B, by joining up all the pivots in Cache A with slicers, then joining up all the pivots in cache B with slicers, then syncing just one pivot on cache A with just one pivot on cache B and let the slicers sync the rest.

Will work it up and post over at DDOE in due course.

 


Comment by: Jack Astill (8/22/2014 5:31:41 PM)

Hi I have been trying to replicate this code for a project of my own for a few days on and off. I've found it really helpful, but if you follow the guide step by step it doesn't mention this part which is in the full script:

"'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"

I am a relative novice for VBA so it didn't occur to me that this was the reason my code was falling on it's face every time. It might just be worth pointing out!

 


Comment by: apandit (11/24/2014 2:34:23 PM)

HEllo

I am trying to implement the above code - however some of the pivot tabels do not have all the field

Pivot table 1. Global, Regional, Sub_Regional, Country
Pivot table 2. has the above + Class_code
Pivot table 3. has Region, Sub_Region and Country

I get random results - in your article it says "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"

Pls advice what is the best way to sync all these slicers.

AP

 


Comment by: Jan Karel Pieterse (11/24/2014 5:06:37 PM)

Hi Apandit,

Well, suppose you selected region "France" in the slicer of PT1 but France is missing from the data for PT2.

It is up to you to decide how you should now "filter" PT2.

One way might be filter PT2 so it shows no data:

Loop through all items of the associated Region field in PT2 and uncheck them so as not to show any data.

 


Comment by: Lars Larson (12/29/2014 5:51:59 PM)

I adjusted the code you included to fit my purposes, of course...thanks.

But I am seeing it throw an error on the line that I have as:

oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True

(1004:Application-defined or object-defined error)

It is acting as thought the object have not been instantiated even though it makes it that far. It makes me think that the version of Excel (VBA) I am using (the latest version) is somehow working differently. that maybe the object model is an older one in this code.

Anyone have any ideas for me?

 


Comment by: Jan Karel Pieterse (12/30/2014 10:37:24 AM)

Hi Lars,

Perhaps the last sliceritem cannot be selected because it is dimmed (due to another filter)?

 


Comment by: ravi asrani (1/16/2015 3:11:20 PM)

1. Added slicer
2. when I select one Criteria in slicer I get data in pivot according to that criteria.
3. But when we double click on the figure for that criteria
it gives full detailed data from that actual sheet instead of the only showing that data as per the criteria.

 


Comment by: Jan Karel Pieterse (1/17/2015 7:29:11 PM)

Hi Ravi,

Odd, as that normally also does not happen without a slicer. A slicer is just an additional way to operate the pivottable filters, the pivottables functionality stays the same.

 


Comment by: CL (4/1/2015 4:00:14 PM)

Hi,
Does anyone have this working in a file they can share please? I am having issues getting the code to work. I have multiple external data sources and multiple slicers, so this looks like a very good solution.

Any help much appreciated.

Thanks,

-CL

 


Comment by: Jan Karel Pieterse (4/2/2015 7:27:52 AM)

Hi Rob,

Drop me a line and I'll send what I have.

 


Comment by: FLORE (6/22/2015 10:38:12 PM)

I never use VBA but your code here is exactly what I need.
However, I am so new that I don t know where to put the code (my slicers are copied into a worksheet but the pivot tables are in another one (this code should be where the slicers are true?)
then my other question is how can I put only one selection field for eac slicers that makes your code run?
in my example I have 10 to 15 pivot tables. These pivot tables are based on 3 data sources, meaning that for the same selection field (I have 3 Country, Station, Months) I have 3 slicers for each filter...)
the idea to make it friendly for other user is to have only one slicer for month, one for country and one for station that allows to synchronise the 9 slicers I had to create.
I hope it is not to confused and you can help me.

 


Comment by: Jan Karel Pieterse (6/23/2015 7:36:37 AM)

Hi Flore,

The code shown all goes into the ThisWorkbook module of your Excel workbook.

 


Comment by: Flore (6/23/2015 1:58:25 PM)

Dear Jan,

Thanks for your answer. I still have 3 questions:

1- in your code the only part I don t understand is the following Dim sYear As String .

I don t follow why it has been added, is it because the Year (in your code) is the only slicer linking all the pivottables (same filter in each one)?
In this case, if I have 3 slicers filtering in each pivottables (some linked with the same datasource and some with others), shall I add the same Dim for each slicers caption name?

2- IS your code valid for slicers that have the same caption name but each slicer comes a different a different data source
example Months from datasource 1, Month from data source 2 and Month from data source 3?

3- If the answer is yes, if I only click on one slicer, all the slicers having the same name will synchronise (eg. Month one)?

Many thanks again for your help

 


Comment by: Flore (6/23/2015 3:54:24 PM)

Your code is just amazing and works (please forget my previous questions except the one).
I have a difference in the setting because one of my slicers caché referring to station is having a slicercaché of station in one slicer and origin_city in the other 2.
moreover the list of station/city is different as a result the same station as the first one is selected but another 2 are also selected in the longer list of cities.
How can I send you the code to see how I can fix it?
Thanks for your help :)

 


Comment by: Jan Karel Pieterse (6/23/2015 5:24:50 PM)

Hi Flore,

1: sYear is not used so the declaration can be removed :-)

2: The code is ONLY needed for slicers which are using different pivotcaches, if they share the pivot cache, tie them together using the Pivottable connections of the slicers.

3: they should :-)

 


Comment by: Flore (6/24/2015 10:26:24 PM)

Sorry, me again...
Your code is working with one of th eslicers but not with the other 2.
I have 3 slicers per each datasource associated to several pivot tables.
Each slicer have the same caption name for each data source
-Months
-Station
-Country
For the month slicer the name "Months" is in allat the same place
For the station one, the slicer caché are the following:
Slicer_Pos_Station_Code / Slicer_Origin_City_Code and Slicer_Origin_City_Code1
For the country:
Slicer_Pos_Country_label / Slicer_Origin_Country_Code and Slicer_Origin_Country_Code1.
I think that it is where I have an issue and the code is not working.
I have tried to manage it in the Mid function and also adding a "or" function to include the City name.
Can you help me adapting your code?
many thanks in advance

 


Comment by: Jan Karel Pieterse (6/25/2015 8:14:01 AM)

Hi Flore,

If you email your workbook I will try to help. However: No promises, as I'm really busy.

 


Comment by: Flore (8/25/2015 3:57:31 PM)

Dear Jan,

Sorry to bother again, I fix the issue I posted on the 25th of June by creating the same field names for each of the data sources. Your macro works very well and I thank you for that.

But there is always a but (I am still not good enough to make big changes by myself) recalculation takes 2 long if I base this macro on the whole work book (the data sources sum more than 350 MB) so my idea is to split this macro in one for the 1st worksheet that needs the synchronization and in another one for the other.
The first work sheet name is equ and the other one is equ HEA
how shall I write this in the macro instead of "Thisworkbook"?
I suppose doing this the synchronization will take place only for the worksheet I am working on true?
Thanks again for your help

 


Comment by: Jan Karel Pieterse (8/26/2015 11:34:00 AM)

Hi Flore,

It depends. A Slicer can be placed on ANY worksheet, not necessarily on the same sheet that the pivottable the slicer controls is on. Also, one slicer can control multiple pivottables on multiple worksheets. So what the code needs to do precisely, depends on your workbook setup and your requirements.

 


Comment by: SJ (9/8/2015 8:06:56 PM)

Hi Jan,

This is really great.

It works perfectly for my purposes.

However, I was wondering if this can be done with timeline slicers.

For regular slicers, multiple slicers get synched, but for timeline slicers, i noticed that this code doesn't work.

Is there a separate name for SlicerCache for Timeline Slicers? Or is this even possible?

Thanks

Sean

 


Comment by: Sean (9/8/2015 8:09:38 PM)

Hi Jan,

Thanks for this wonderful post.

I was able to adapt this code for my purposes.

I just have one follow up question.

Can you do this for Timeline Slicer? I was able to do it for regular slicers, but not Timeline Slicer, with all the parameters you described consistent.

Once again, I appreciate this post.

Thanks!

 


Comment by: Arthur (9/15/2015 9:38:14 PM)

This looks really neat and almost got it to work. Can you send an example excel file with the synchronizing slicers?

 


Comment by: Dmitry (12/26/2015 9:51:45 PM)

Hi,

Many thanks for the effort in writing this code. It perfectly suits my case, but doesn't SOLVE it.

I tried 5-6 macros (went deeply into Googling) that would solve my case, but non of them could synchronize / mimic / mirror the selected items on the Slicer.

Just a few facts:
- 1 data-set responsible for 3 pivots (and 2 pivot charts from those), another data-set responsible for another pivot
-1 data-set contains weekly values, second data-set - monthly
- both contain IDENTICAL parameters as Forecast Period, Country, Location, and Product
- slicercaches names are for example: Slicer_Forecast_Period, and Slicer_Forecast_Period1

Assuming that everything is the SAME I should be able to have only 1 slicer instead of 2.

Finally, the code provided by you was adjusted to suit my case. And I'm not new to VBA, got some basic experience.

Why it doesn't work? Any ideas...?

Cheers,
Dmitry

 


Comment by: Dmitry (12/27/2015 1:22:23 PM)

Goedaag Jan Karel,

Please ignore my previous message / comment. I made some progress and macro is now triggered. However, I'm getting an error "Method 'Selected' of object 'SlicerItem' failed" on the line

oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True 'error here
    For Each oSi In oScForecast.SlicerItems

 


Comment by: Jan Karel Pieterse (12/28/2015 3:59:07 PM)

Hi Dmitry,

For slicers tied to the datamodel (Excel 2013 and up), the Selected property does not work. I'll have to look if I can find a working example.

 


Comment by: Dmitry (12/28/2015 4:11:53 PM)

Dear Jan,

I'm using Excel 2011, not 2013. :)
concerning the "Selected" property, I've tried usinng
oSc.SlicerItems.Item(oSc.SlicerItems.Count).Selected = True

and
oSc.SlicerItems.Item(oSc.SlicerItems.Count).Select


Non helped.

 


Comment by: Jan Karel Pieterse (12/29/2015 11:52:46 AM)

Hi Dmitry,

Perhaps you can use the VisibleSlicerItemsList property:

ActiveWorkbook.SlicerCaches("Slicer_IMT").VisibleSlicerItemsList = Array( _
        "[Table1].[IMT].&[IMT_1]")

 


Comment by: rene vis (3/22/2016 5:24:07 PM)

Dag Jan Karel,

net vandaag heb ik onder naam 'Rene' een post gedaan op StackOverflow over dit onderwerp gaat. Zie http://stackoverflow.com/questions/26810376/synchronizing-slicers/36157201#36157201

Ik heb een andere aanpak gekozen voor het probleem: "Approach is by saving state of all slicer cache objects. After a change in a pivot table new states can be compared with old states and a list of updated caches composed. Once this list is known, synchronizing can be accomplished."

Met dank aan je voorbeeld, Rene Vis

 


Comment by: Jan Karel Pieterse (3/27/2016 8:20:13 PM)

Hi Rene,

Nice example. Though I wonder whether putting the selected items into collections might be more efficient than a concatenated string.

 


Comment by: Allison (6/27/2016 11:35:14 PM)

This sounds like it is what I need, but I am not quite sure. I am just beginning to learn VBA, had some experience working with a program Boston Workstation that utilized some VBA, basically an interface that helped you build VBA scripts easier, but not experienced. Anyway, my issue is that I have a dashboard that is summarizing data the is pulled from multiple different pivot tables via the getpivotdata function. I have two caches, because I have to isolate some of the data from the original data, all the same data fields, just a subset with specific sorting and duplicates removed etc. I have two slicer's "Campus" and "PRIMARY_PAYOR" that all I want to do, is make the slicer's mirror each other, as in, if I select a location on the "Campus" slicer that is on the dashboard, the second campus slicer that is hidden on another tab is also changed to that location, that way, all the data showing on the dashboard is for that location, and same with the payor. Can you help me adapt this code for my purposes?

 


Comment by: Jan Karel Pieterse (6/28/2016 8:53:49 AM)

Hi Allison,

That is precisely what the code example is for. So yes that should be possible. What you need to know is the name of both slicers.
Right-click the slicer and select Slicer settings. Record what is written next to "Name to use in formulas" and use that in the code.

 


Comment by: Ron (6/28/2016 2:19:56 PM)

Hi Jan Karel,

I used this code to manage 6 slicercaches with two slicers and it works great.
There is one thing that is not working I guess: event looping.

In my case the code is run 7 times where after the first run all slicercaches/pivots are already set to the filtering.
I think it is due to having on one slicercache a third slicer (3rd level of detail only available in one pivot data set) but I can't get it to work.
Any ideas?

 


Comment by: Jan Karel Pieterse (6/28/2016 4:20:39 PM)

Hi Ron,

You should be able to prevent event looping by using Application.EnableEvents = False in the beginning of the event sub. Don't forget to turn it back on at the end!

 


Comment by: Ron (6/28/2016 4:47:55 PM)

Hi Jan Karel,

I tried that but didn't work. The events are executed after the code Sub Workbook_SheetPivotTableUpdate has finished executing. So sequential events.

It seems to be related to the amount of pivots related to a the slicercache of which the slicer changed.

I have a sheet with 7 pivots based on one slicercache. When changing a slicer on that sheet it appears all pivots trigger the Workbook_SheetPivotTableUpdate sequential. On sheets where the slicer is related to one pivot the code is run only once.
In both cases all slicers are synchronized and all pivots are updated.

 


Comment by: Jan Karel Pieterse (6/28/2016 5:21:56 PM)

Hi Ron,

Ah, yes, of course. One thing you could do is test for Target.Name inside the event and only allow the event code when it equals one pivottable's name.

 


Comment by: Allison (6/28/2016 5:41:34 PM)

Ok, that is what I thought, I changed anything that say "year" to the name of my slicer "campus", however it doesn't seem to be executing? I don't get any errors or anything, it just doesn't seem to be calling the event.

 


Comment by: Ron (6/28/2016 6:19:58 PM)

Hi Jan Karel,

I don't understand.
When testing for a valid pivottable's name all the 7 on that sheet will be considered valid and changed so triggering the event.
Or am I missing something.

 


Comment by: Ron (6/28/2016 8:05:47 PM)

I guess I solved the 7 pivots on one sheet with one slicercache by only allowing the first pivot on the active sheet to run the event code. Line added to run first in the event:

If Sh.PivotTables(1).Name <> Target.Name Then Exit Sub


I have also a variant where one slicer has two pivots on different sheets. This is not covered by this solution.

 


Comment by: Jan Karel Pieterse (6/29/2016 9:32:05 AM)

Hi Ron,

Excellent, that is what I intended.

 


Comment by: Jan Karel Pieterse (6/29/2016 9:33:03 AM)

Hi Allison,

Are you sure the event code is in the right place? (ThisWorkbook module)

 


Comment by: Ron (6/29/2016 10:23:43 AM)

Hi Jan Karel,

I also found a solution for the unwanted trigger when having pivot on different sheets having the same slicer.
Just allow the event to run on the actual sheet where the slicer was triggered.

This conludes in the following code to be included at the beginning of the event:

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)

    Dim oScVPCS As SlicerCache
    Dim oScTeam As SlicerCache
    Dim oSc As SlicerCache
    Dim oPT As PivotTable
    Dim oSi As SlicerItem
    
    'Prevent event looping on multiple pivots on actual sheet related to one slicer only allow the first pivot
    If Sh.PivotTables(1).Name <> Target.Name Then Exit Sub
    'Prevent event looping on pivots on more than one sheet related to one slicer only allow the pivot on the actual sheet
    If Sh.Name <> ActiveSheet.Name Then Exit Sub
    'Prevent event looping, changing a slicer in this routine also triggers this routine
    If mbNoEvent Then Exit Sub
    
    mbNoEvent = True
    
    Application.ScreenUpdating = False
    .....
    ...


Maybe you can add this to your example so others can have benefit of it.

 


Comment by: Anastasiia (10/4/2016 4:24:29 PM)

Hi,
Thank you so much the macro is really helpful.
I use this in one file with 4 slicers and it was great. But I have an issue with the other one.
I have one Pivot based on one source and 8 more based on the other (common for all 8 pivots). I need to add 4 slicers which should work for all these pivots. With this VBA code I synchronized the slicers and they shows the same data but not exactly what I choose. Foe example, if I chooe 2016 as a year it shows the 2016,2015 and 2014. Or just 2016 and 2015. The same is for other slicers.
I'm not very familiar with the VBA so could you please help me with this issue!
Thank you

 


Comment by: Jan Karel Pieterse (10/5/2016 11:13:06 AM)

Hi Anastasiia,

If you like you can email your file to me and I'll have a look.

 


Comment by: Ibrahim Omar (11/22/2016 6:54:11 PM)

Thank you so much for this Jan!

I want to do the same but for a slightly different case. Instead of synching 3 slicers, I have four pairs of slicers, and I need each pair to be synched.

I have a pivot table with 4 slicers: Type, State, City & ZipCode. I also have a normal data table, not pivot, with the same fields and 4 identical slicers.

I want any selection on the pivot table slicers to be synched in the data table slicers and vice versa.

What would be the altered version of your code for this, given I don't understand VBA at all?

Thanks so much in advance.

 


Comment by: Jan Karel Pieterse (11/22/2016 7:14:27 PM)

Hi Ibrahim,

I could write the code for you on a commercial basis if you like?

 


Comment by: John Bentley (11/24/2016 10:12:29 AM)

Fantastic bit of code, really helped me out. Thanks!

One thing that caught me out that this article may benefit from highlighting, where the code is looking at the slicer names -
Mid(oScKwrt.Name, 7, 3)
- it is the "Name to use in formulas" name, rather than the free-text name (which I had renamed prior to using your code).

It left me scratching my head for a little while until I realised.

Works like a charm now. Thanks Jan!

 


Comment by: David (12/22/2016 5:43:08 AM)

Whoever wrote this tutorial just saved my last two months of work !! I wanted to synchronize Slicers, but I had this issue of all items selected when looping through the slicers. So I've made a macro running through each pivot filter table to change directly there, but the calculation ended up endless.
Now you have explained the trick of selecting the last slicer item, I'm saved.

Thank you very much !

David

 


Comment by: Dan (12/27/2016 7:42:10 AM)

Hi Jan,

Fairly new to the VBA and trying to implement the code for 8 slicers and pivot tables from 2 data sources....My slicers are Brand, Category and Manufacturer....Do i need to use Slicer_Brand, Slicer_Category...in the codes or ust plain Brand, Category ie...also where do i include the names of the pivot tables in the code?

I am getting run time error 91...object variable or with block variable not set error.

Thank you for your help

 


Comment by: Jan Karel Pieterse (12/30/2016 3:08:22 PM)

Hi Dan,

You can find out the name of a slicer from its settings(right-click a slicer, select Slicer Settings... look for "Name to use in formulas".

 


Comment by: Dan (1/4/2017 8:25:08 PM)

Thank you Jan! For some reason when i modify the code and run it I get Run Time Error 1004 and below "oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True" is highlighted in yellow. What may cause this?

Thank you

 


Comment by: Dan (1/4/2017 9:49:41 PM)

Thank you

 


Comment by: Dan (1/5/2017 12:05:54 AM)

Hi Jan,

In my case the values in Slicer 1 differs from Slicer 2 what would be the workaround for this problem.

Thanks

 


Comment by: Jan Karel Pieterse (1/5/2017 6:24:09 AM)

Hi Dan,

The RT error may be caused if your slicer is driving a Powerpivot pivot table or a pivot table based on more than one table. Maybe this suggestion helps:

http://www.jkp-ads.com/articles/slicers04.asp?AllComments=True#23596

 


Comment by: Jan Karel Pieterse (1/5/2017 6:45:30 AM)

Hi Dan,

I guess that is up to you. If items are missing from slicer A, would you select them on B? I'd suggest a workaround where you add a third (hidden?) pivottable which contains all items of both slicers and use a slicer to that table as the main slicer to control the others. The slicers which are possibly incomplete should not be visible to the use IMO. To make it transparent to the user, I recommend to add visual clues (on the cell grid) if items are not available in a pivot table which do show on the slicer.

 


Comment by: Bruno (1/19/2017 6:02:35 PM)

from you initial post
"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."

well, it's EXACTLY what I need.
assuming I have everything on the same data set (a column for "quarter" e another for "month") is it doable?

I just want to be able to set the month slicer and the quarter slicer adjusts automaticaly.
Maybe you can point me in the right direction as I could not find it online for a VBA (very) newbie.

Congrats on the excelent post.
Bruno

 


Comment by: Jan Karel Pieterse (1/20/2017 11:35:02 AM)

Hi Bruno,

If the pivottables are connected to the same pivotcache, all you need to do is add both Q and M slicers and tie them to all pivottables. They'll sync automatically without need of VBA

 


Comment by: Bruno (1/20/2017 12:00:43 PM)

Hello, thanks a lot for your fast response.

you ALMOST solved my problem.
I made 2 pivotables, one for quarter, one for month
2 slicers, one for quarter, one for month and connected either one to both pivot tables.

it works, but only one way.
if i choose quarter 1, the other slicer will adjust to the corresponding months (but they have to be selected first, or a clean filter)
I would like to be able to choose month 1, or 2 and the quarter would adjust to the corresponding quarter (it does) with the total of the quarter (it doesn't). instead it presentes the same monthly value.

i was testing as i wrote this and found a workaround.
Added an extra column with quarter total for each month line and had that in the pivottable.
problem solved.

 


Comment by: Jan Karel Pieterse (1/20/2017 3:39:22 PM)

Hi Bruno,

Ouch, I apologise, this is indeed not the right solution, as the Month slicer will filter for that month on all attached pivot tables. You would need something like this:

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 oCell As Range
    Dim oPT As PivotTable
    Dim oSi As SlicerItem
    Dim sYear As String
    Dim bUpdate As Boolean
    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
                Else
                    Set oScKwrt = oSc
                End If
                Exit For
            End If
        Next

continued in next message...

 


Comment by: Jan Karel Pieterse (1/20/2017 3:40:34 PM)

Continued from previous message...

        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
                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

Continued on next message...

 


Comment by: Jan Karel Pieterse (1/20/2017 3:40:45 PM)

Continued from previous message...
    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
    '    ChangeQuarters
    mbNoEvent = False
    Application.ScreenUpdating = bUpdate
End Sub

 


Comment by: Bruno (1/20/2017 3:54:40 PM)

Thank you very much.
I managed with the workaroud that I mentioned but I'm DEFINITELY going to bookmark this for future use.

Cheers,
Bruno

 


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].