Learn Excel – Sync Slicers from Different Data Sets – Podcast 2104

Learn Excel – Sync Slicers from Different Data Sets – Podcast 2104


Learn Excel for MrExcel Podcast, Episode 2104:
Synchronize Slicers from Different Data Sets. Hey, welcome back to the MrExcel netcast,
I’m Bill Jelen, and today’s question is not about how to take these two pivot tables that
came from one data set and make the Slicer control all those pivot tables. That’s not what this is about. That’s an easy thing to do– Slicer, Tools,
Options, either Report Connections or Slicer Connections in the old version, and check
that you want this Slicer to control all of those pivot tables. Easy, right? This question is about this worksheet, where
we have two different data sets and we’re going to create a pivot table from this, and
from this– now let me speed up the video while I create these pivot tables. Alright, now, what you’re going to see is,
I have two pivot tables, this pivot table is created from one data set, and there’s
a slicer that controls that pivot table; and then I have a second pivot table that’s created
from a different data set, and a slicer that controls that pivot table. But there’s absolutely no way to get this
slicer to control both this pivot table and this pivot table that’s built from a different
data set. Alright. But I’m going to show you how to do that today
with a macro. Now, this is tricky to do. When the question came in, I said, “Now, this,
I don’t think you can do it.” But I’ve been working on it and experimenting
and I think I finally got it. I have to think I finally got it down. Alright, so let’s go through this. First off, this is saved as an xlsx file. That’s a fine file-type, except for it’s a
horrible file-type because it’s the only file type that doesn’t allow macros. You have to change this from xlsx to xlsm,
or all of your work to the rest of the video is going to be thrown out the window. Save as, change the file type to xlsm or,
heck, xlsb, either one of those will work. That’s the one that is broken– xlsx– and
it’s the default, crazy isn’t it? Xlsm, click Save. If you’ve never done macros before, Alt+T
for Tom, M for Macro, S for Security and you will be able to save all macros without notification. Need to change that to the second one, that
will allow your macros to work. Alright, now we have two slicers. Bet you never knew this, but slicers have
names. We’re going to go to Slicer Tools, Options,
Slicer Settings, and see this one is called Slicer_Name. Like that. Go to the second one, go to Slicer Tools,
Options, Slicer Settings, this one’s called Slicer_Name1– not Name space 1, Name1. Two names like that. Here’s what we’re going to do. We’re going to switch over to VBA– Alt+F11. In VBA, if you’ve never done VBA, you’re going
to have this big gray screen. We’re going to come here and say View, Project
Explorer, in the Project Explorer find your file– mine’s called Podcast 2104. Open Microsoft Excel Objects, and the sheet
where I want this to work is called Dashboard. I’m going to right-click there and say View
Code. This code that we’re writing cannot go in
a module like in a regular macro– this has to be on this worksheet. Open the top left drop-down, Worksheet, then
in the top right drop-down, we’re going to say Pivot Table Update. Alright, so this is where our code’s going
to go now. I’ve already pre-baked this code. Let’s take a look at the code here in notepad. So, we’re going to have two Slicer caches–
SC1 and SC2– one Slicer item and then, right here, this is where you’re going to have to
customize it. So my two Slicers were called Name and Name1. Alright, you’re going to have to put your
slicer names in there. Application.Screenupdating=False, Application.EnableEvents
=False, and then Slicer Cache 2– we’re going to clear the filter, and then for each item
SI1 and sc1.SlicerItems, if it’s selected, then we’re going to make the same item in
Slicer Cache to be selected. This is a little loop that will run through
however many items happen to be in that slicer. In my case, I have 11 or 12; in your case,
you might have more. When we’re done with that, turn enable events
back on, turn Screen Update back on. Alright. So, we’ll take this code, copy this code and
paste it here in the middle of our macro like that. Alright, now, let’s just make sure I’m going
to press Ctrl+G and my ask for is Application.EnableEvents, on or off– so, ? Application.EnableEvents–
and it’s true. If yours comes up as false, then you want
to come back up here and say that it’s=True– so, then, you’re turning those events on. Alright. Now, here’s what’s going to happen. So our coach should be working here, it’s
on the right worksheet. We’re saved in an xlxm file, and I turned
Macros on and what we’re going to see, is that when I choose from the left Slicer, that
Slicer Cache 1– I’ll choose Andy through Della– the other Slicer is going to update
as well. Alright And even if I would choose just Gloria–
just Gloria– it looks like it’s working really, really well. Even if I would CTRL+click, when I let go
Ctrl, they all three will update. But here’s the gotcha– there’s always a gotcha–
this Slicer, it has to exist, but you cannot use this Slicer– wait, I mean you can, you
can use a Slicer but it’s going to confuse the heck out of things. Because what’s going to happen is I’m going
to change this to Hank and they’re going to go back to whatever is in Slicer Cache 1,
because I changed the pivot table on this sheet. Now, in real life, are you going to have two
pivot tables on the same sheet? I don’t know if you are or if you aren’t,
alright, but things are going to become a little crazy. Now, let’s just take a look at this. First thing I want to do, is I’m going to
insert a new worksheet– Alt+IW for inserting the worksheet– and I’m going to call this
a DarkCave. You can call it whatever you want. I’m going to take that dashboard that’s not
going to work, I’m going to copy that dashboard and come here to the dark cave and paste it
there and then right click and hide that sheet so no one ever sees that Slicer. And then, from here, we should be able to
delete it. Nice, alright. And we’re going to just check to make sure
they’re still working– choose Charlie through Eddie and they’re both still updating. Now, what’s happening? The Slicer that we can’t see, the one that
we’ve hidden away, it’s updating as well, but we don’t care that it’s updating. Now, what if you want to have your things
on different sheets? I’ll insert a new worksheet here–Alt+IW–
and I’ll take one of these pivot tables– maybe the second pivot table– and move it
to that other sheet– so, Ctrl+C to copy the pivot table, Ctrl+V to paste the pivot table
here. And if I need to have a slicer here– don’t
insert a slice from this pivot table– we have to come back to our dashboard, take the
slicer that’s the controlling Slicer, Ctrl+C to make a copy of it, and paste it here–
Ctrl+V. Alright? Now, we have no code on this sheet– there’s
no code on Sheet4– and I was thinking I was going to have to add some code to Sheet4,
but here’s the beautiful thing: When I change this slicer, what’s happening is, on the dashboard
that pivot table’s updating even though that pivot table on that sheet that’s not active
is updating, they will run the code and this will update as well. Pretty darn amazing that that works. Now, the whole key to this is, you can never
use the slicer tied to the second pivot table. You have to have the slicer that’s tied to
the second pivot table but you cannot use it– you have to use this slicer tied to the
first pivot table. Alright? But in general, I think this is working fairly
well. Alright, now hey, Sal, the person who asks
this question, wrote in and said, “Look, I have a disconnected pivot table– disconnected
slicer in the second pivot table only.” So let’s just add a new field here called
Region, East, West, we’ll refresh our second pivot table, cool, and I’ll insert a slicer
that is disconnected– in other words, it’s only in the second data set, not in the first
data set, Alright, now, this is going to be tricky because when I choose East from here,
we’re not going to have anybody selected. Alright? So the pivot table goes away. I would have to clear this slicer on the left
hand side and then East remains selected. And now things have gone to hell, right? So, you are choosing from a slicer tied to
the second data set and, while the second one is updating, the first one is not going
to respect that because it has no idea there’s no region filled back in the other field. This is only going to work when you have the
same field in both data sets. If you have some other situation like this,
then it will not fly. So here’s what you’re going to have to do:
You’re going to have to insert that field– the Region field– back in your original data
set, refresh this pivot table, insert a new slicer that will control that first pivot
table. Alright? Now, we have two different slicers now, and
because I built them backwards their names are backwards– this one’s Slicer_Region 1,
and the one that’s going to be the controlling one is called Slicer_Region2. If I would have planned differently, we would
have had a different result, but here we are. Alt+F11, I want to take a lot of cutting and
pasting. I’m going to take those first three lines
and paste them, change it to SlicerCache3, SlicerCache4, SlicerItem3. I’ll initialize SlicerCache3, ClicerCache4
to be Region2, Region1, clear the manual filter on SC4– so that was a copy and paste, take
this entire loop here and paste it. There are a lot of places you have to change–
your SI3, SC3 and then SC4, SI3. SI3– don’t miss that one, I missed that one–
next SI3. Alright, so now this set of code will hopefully
control two sets of slicers. If you had a third set of slicers you’re going
to do the same changes I just made, copying and pasting and changing things carefully. Carefully. And again, now, this this guy is the one that
we will never see– we never want to see that one work– because the ones on the Pivot Table
1 are the controlling ones. So this, we have to copy this– Ctrl+C– go
to our sheet where we’re hiding things away– so Home, Format, Hide and Unhide, Unhide that
sheet (the DarkCave), Paste so it continues to exist, it has to live somewhere, and then
once I know it’s back there on the DarkCave I can delete it and then hide this sheet here. Alright, and so now we should have on our
dashboard, one set of slicers, we choose Central, they both update; we choose Just Flow, they
both update; I clear the filter and Central stays. That’s actually good. I’m glad that works– clear this filter and
everybody comes back. But these all have to be driving off that
first pivot table. What if you have a field in the second data
set that’s not in your first data set? Then all bets are off. We’ll go back to “I don’t know how to solve
that”. Well, hey, Macros came to the solution today
and Macros are amazing and awesome. If you want to learn all about Macros, Tracy
Syestad and I have written this great book, “Excel 2016, VBA and Macros.” Check that out, Click the “I” on the top right
hand corner to get to a page where you can buy that book. Alright, Episode recap. How can you have a slicer drive two pivot
tables? If they both came from the data set it’s simple–
Slicer, Report Connections, Choose Other pivot tables. But if a pivot table came from two data sets,
lots of steps change– xlsx to xlsm, change your macro security setting, Alt+F11 to get
the VBA, Ctrl+R to display the Project Explorer, find the worksheet name that contains your
first pivot table and slicer, right-click and say View Code, and then Insert code for
worksheet, Update, then, really important, Hide that second slicer away on a hidden worksheet
or far out to the right so no one can ever choose from that slicer. By the way, don’t cut that slicer– you have
to copy it and paste and then delete the first one in order to get it to work. Want to thank you for stopping by, we’ll see
you next time for another netcast from MrExcel.

38 thoughts on “Learn Excel – Sync Slicers from Different Data Sets – Podcast 2104

  1. Thanks for putting this together. I had heard whispers that there was some way to do this with VBA, but I eventually abandoned my quest to have this done.

  2. Awesome solution!!! Thank you Mr. Excel!! I can easily do this in Power Pivot (using as a add-in) for Excel 2010 but the workbook will get distributed to many people at work who don't know Power Pivot and it's not feasible to ask them to download the add-in.  Your solution will help automate various pivot tables from multiple sources.  Much appreciated 🙂

  3. Hello Mr. Excel, It's not necessary to cut/past the unused slicer to another sheet. You can simple go to Options-Selection Pane and hide the slicer.

  4. btw Could you maybe do a video on how to add criteria (string variable) to a Pivot Table Filters with VBA when created from the data model?

  5. Thanks for the solution Bill! I'd think this could also be done with Power Query to create a single unique table of the names and create a relationship to the two different table with a pivot for each and a slicer for the unique table of names.

  6. amazing, this has been serious puzzle before. MS should work on solution how to somehow consolidate slicers of different datasets.

  7. Hi MR Excel
    I have Question
    if you want to color some words in same cell you highlight this words and change color
    but if this cell contain formula i can't do it
    so, is that any way to do that ???

  8. Hi Bill. You should also set the PivotTable.ManualUpdate property for any Pivots connected to the Slicer to TRUE before you start, and back to FALSE when you're done, or each PivotTable tries to update after each and every slicer item is changed. You won't notice much difference on small datasets, but you will on big ones. Bigtime!

  9. Hey Bill,

    All my pivot tables are on different sheets than my slicers, does this make a difference? The code just seems to do nothing for me.

  10. i keep getting a run-time error '5' Invalid procedure call or argument for this line:
    sc2.Sliceritems(SI1.Name).Selected = SI1.selected

    Anyone else having this issue?
    Macros are on, .xlsm filename, enableevents = True

  11. Hi Bill,

    Thanks for this, been looking for this type of solution for ages!

    I'm falling over at 'For Each SI1 In sc1.SlicerItems'

    SI1 = nothing.

    Have attempted all solutions provided on this page??

  12. Hi Bill, I get this error: "Run-time error 404: Object required" in this line: "For Each SI1 In sc1.SlicerItems"
    Do you have some suggestion?

  13. Is it possible for 2 pivots on the same worksheet to use the same slicer (in the same way you have shown) when the data for each pivot is on a separate worksheet? Sheet 1 = Data source 1, Sheet 2 = Data source 2, Sheet 3 = Pivots for both data sources

  14. Hi Bill

    Thanks a lot

    Unfortunately I keep having error for the last line ( sc2.SlicerItems (ST1.Name).Selected,=ST1.Selected)

    I have already tried.all your tips( and item in my two slicers are equal)

    Would you be kind and think about solutions?

    Was very excited about that…

  15. Hello,When I try to implement this code, I get the following error:
    Run-time error '5': Invalid procedure call or argument
    When I try to debug it, the line that is highlighted is:
    sc2.SlicerItems(SI1.Name).Selected = SI1.Selected
    I should not have any input errors because I copy-pasted the code off your website.
    I have a slicer (slicer1) that is connected to 3 different PivotTables in separate worksheets (that all have the same data source). I need to synch that to a second slicer (slicer2) that is connected to 2 other different PivotTables in separate worksheets (that have the same data source that is different from slicer1). Both slicers are working off the same parameter 'Province'.
    Both slicers are on a 'dashboard' worksheet that is a separate sheet from the sheets where the PivotTables reside. Where should I be pasting this code? In just one of the Primary PivotTable sheets? In all of the Primary PivotTable sheets? In the dashboard sheet?Is there any way to make this code run under my scenario?
    Thanks in advance!

  16. When i run this code its giving me a run time error-1004: Application defined or object defined error for the line below highlighted.

    For Each SI1 In sc1.SlicerItems

    any thoughts on how to resolve this error.

  17. Hello and thanks. Curious if you (or anyone) has come up with a solution for the error many are having with the line “For each SI1 In sc1.Slicer Items”

    I am 100000% sure it is not a typo, but this line will not allow my code to proceed.

  18. Thank you!!! this is great! i have 2 pivot tables with 2 data sources and I needed to do this. The only problem is the VBA disables my filters. Is there a way to not disable the filters? so that selection is driven by the filter drop down instead of the slicers?

  19. This is really cool thank you for this, just a quick question. i have 3 fields, name, month, year and each links to 3 different data sources. I want to basically apply this same concept but have say sc1 apply to sc2 and sc3. How can i do this?

    This is my code:
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim sc1 As SlicerCache
    Dim sc2 As SlicerCache
    Dim sc3 As SlicerCache
    Dim SI1 As SlicerItem

    Dim sc4 As SlicerCache
    Dim sc5 As SlicerCache
    Dim sc6 As SlicerCache
    Dim SI2 As SlicerItem

    Dim sc7 As SlicerCache
    Dim sc8 As SlicerCache
    Dim sc9 As SlicerCache
    Dim SI3 As SlicerItem

    Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Month21")
    Set sc2 = ThisWorkbook.SlicerCaches("Slicer_Month22")
    Set sc3 = ThisWorkbook.SlicerCaches("Slicer_Month2")

    Set sc4 = ThisWorkbook.SlicerCaches("Slicer_Agent_Name4")
    Set sc5 = ThisWorkbook.SlicerCaches("Slicer_Agent_Name1")
    Set sc6 = ThisWorkbook.SlicerCaches("Slicer_Agent_Name")

    Set sc7 = ThisWorkbook.SlicerCaches("Slicer_Year1")
    Set sc8 = ThisWorkbook.SlicerCaches("Slicer_Year2")
    Set sc9 = ThisWorkbook.SlicerCaches("Slicer_Year")

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    sc2.ClearManualFilter
    sc3.ClearManualFilter
    sc5.ClearManualFilter
    sc6.ClearManualFilter
    sc8.ClearManualFilter
    sc9.ClearManualFilter

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

    For Each SI2 In sc4.SlicerItems
    sc5.SlicerItems(SI2.name).Selected = SI2.Selected
    sc6.SlicerItems(SI2.name).Selected = SI2.Selected
    Next SI2

    For Each SI3 In sc7.SlicerItems
    sc8.SlicerItems(SI3.name).Selected = SI3.Selected
    sc9.SlicerItems(SI3.name).Selected = SI3.Selected
    Next SI3

    Application.EnableEvents = True
    Application.ScreenUpdating = True

    End Sub

  20. Please can someone help. I open the workbook, click slicer. Get runtime error 5. Click end and all slicer change as if it working. But only works that once. Debug takes me to the below for each. Fyi.. i have 4 tabs of data all with same headings. 1 tab with pivots and slicers. The code is running om the pivot tab. Debug line = sc3.sliceritems (SI1.Name).Selected = SI1.Selected.

  21. Excellent video! However I keep getting the error "Run-time error '5'. Invalid procedure call or argument" on the line "for Each SI1 In sc1.SlicerItems …", Do you know how to fix it? 🙂

  22. Hello, thank you for doing this video – it is incredibly easy now, HOWEVER, when I add in a 2nd slicer, the code mentioned in the link and the video then falls over. Please could you help?

  23. Hello! Thank you for this extremely helpful video.
    One question though: what if I would like to sync a third slicer to the first two? So if someone selects something on the first (main) slicer, the other two automatically sync (as opposed to just one slicer being synced with the main)

    ex. if you had a "Slicer_Name2" in addition to the first two slicers. How would you go about changing the code to apply to all slicers.

    Thank you very much.

  24. Help! So what is the naming convention when you add a second set of slicers to filter a different field? He adds two more and they're called slicer cache 3 and 4, with the main slicer item being 3. I am in a situation using FOUR tables, and want to add a second slicer so what's the convention for that? I have in my first set slicer cache 1 thru 4, with slicer ITEM one. Then for me next set of slicers I have slicers 5 thru 8 with the next slicer item being 5. Is this named right? It's giving me an error when I apply that naming logic to the second loop

  25. Sir thanks for this video, i have a slightly different request may be you can help me… from your example you are using names in the first PT to modify data in the second PT [both using NAMES]. what would be the codes if you had from PT1 select region to get a list of names and use results names to modify PT 2? thank you in advance Thierry

  26. Please help getting error at line : For Each SI1 In sc1.SlicerItems

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

    ' These names come from Slicer Settings dialog box
    Set sc1 = ThisWorkbook.SlicerCaches("Slicer_gender")
    Set sc2 = ThisWorkbook.SlicerCaches("Slicer_gender1")

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    sc2.ClearManualFilter

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

    Application.EnableEvents = True
    Application.ScreenUpdating = True

    End Sub

  27. I got this to work, which solved a problem I was trying to fix for awhile. However, it takes an unacceptable amount of time to execute. My slicer is for State, so there are 51 different options. It takes roughly 10 – 15 seconds to execute. Is this expected due to the number of options (51)?

  28. Has anyone figured out how to do this when the Slicer options has a space? I have a dashboard that uses this VBA to sort two tables with one slicer. The challenge is that my slicer items are "Firstname LastName" which is giving me the "Run-time error '5'. Invalid procedure call or argument" on the line "for Each SI1 In sc1.SlicerItems …" How can I change the code to look at the entire field is slicer 1 and do that to slicer 2?

Leave a Reply

Your email address will not be published. Required fields are marked *