Over at the Excel Guru forum, Yewee asks:
I have 3 sheets in my excel worksheet.
1. Org
2. DataSource
3. Pivots Table
My Pivot table will get the data from the DataSource sheet. I will like to have the filter of the Pivot Table from one of the cell in Org Sheet.
How can I do that?
Incredibly easily, if you have Excel 2010 or later…because:
- a PivotTable with nothing but one field in the Filters pane looks and behaves pretty much exactly like a Data Validation dropdown does; and
- that PivotTable can be hooked up to the other PivotTables via slicers, so that it controls them.
If you’re a long-time reader of this blog you probably already know that, and may want to skip to the end to find a bit of VBA that makes setting up Slicers slightly more easy. But if you came here via Google, then pull up a pew and read on.
So let’s say these are the two Pivots that you want to control via a dropdown, and you want to put the dropdown where the red rectangle is:
First, create a new PivotTable from the datasource that the other pivots share (or make a copy of one of the existing Pivots) and in the PivotTable Fields pane add the field you want to filter the other Pivots by to the Filters pane. (If you created this Pivot by copying another, remove any other fields that might appear).
Great: Now you have a PivotTable masquerading as a Data Validation Dropdown. From now on, I’ll call it the ‘Master Pivot’. So just drag that Master Pivot where you want it:
From the ANALYZE tab of the PivotTable Tools contextual menu in the ribbon, click the Insert Slicer icon:
…and from the menu that comes up, choose the field name that matches the field you put in the Master Pivot:
…and your slicer will magically appear:
Now we connect that Slicer to the other PivotTables. To do that, right click on the Slicer that just appeared, and click the Report Connections option:
You’ll see from the Report Connections box that comes up that currently it’s only connected to one PivotTable – which of course is the Master PivotTable that we used to insert the slicer in the first place:
What we want to do is connect it to the other PivotTables, by checking those other checkboxes:
(Optional) We might want to make it so that the user can only select one thing at a time by clicking on the Master Pivot filter dropdown, and unchecking Select Multiple Items, if that’s your intent:
…and now all we need to do is move that Slicer somewhere out of sight (but don’t delete it):
Now when we select a region from that Master Pivot dropdown…
… all the other Pivots are filtered to match:
That’s it…job done. As simple as possible, and no simpler.
Actually that’s a lie…unless there’s a good reason not to, it’s much simpler just to use a Slicer in the first place, and not bother with setting up the Master Pivot dropdown at all:
Of course, that Slicer takes up much more room than our Master Pivot dropdown. So maybe that’s a good reason to use the Master Pivot approach, and not a slicer. Especially if we might want more than one dropdown to control all the Pivots and space is at a premium:
Or you can do away with the Master Pivot altogether, and just set the slicers up between the actual ‘output’ pivots themselves, so that as soon as they change a PivotFilter setting in one of the Pivots, the others get changed too. (Note that this also happens with the ‘Master Pivot’ approach…it’s just that we don’t actually need to have that Master Pivot sitting there taking up space at all).
Programatically add and connect Slicers
I’ve always found it annoying that there’s no right-click option to add a Slicer to the currently selected PivotField. Plus connecting Slicers to multiple PivotTables is a drag. And also, I hate it how it adds new Slicers over the top of old slicers. So here’s some code that remedies all that:
Sub AddSlicer()
Dim pt As PivotTable
Dim ptOther As PivotTable
Dim pf As PivotField
Dim pc As PivotCache
Dim rng As Range
Dim sc As SlicerCache
Dim varAnswer As Variant
Dim bFoundCache As Boolean
Dim rngDest As Range
Set rng = ActiveCell
On Error Resume Next 'in case user has not selected a PivotField
Set pt = rng.PivotTable
Set pc = pt.PivotCache
Set pf = rng.PivotField
On Error GoTo 0
If pt Is Nothing Then Exit Sub
If pf.Orientation <> xlDataField Then
Set rngDest = Intersect(ActiveCell.EntireRow, ActiveCell.Offset(, ActiveCell.CurrentRegion.Columns.Count + 1))
On Error Resume Next 'SlicerCache might already exist
With rng
If pt.PivotCache.OLAP Then
Set sc = ActiveWorkbook.SlicerCaches.Add2(pt, .PivotField.CubeField.Name)
Else: Set sc = ActiveWorkbook.SlicerCaches.Add2(pt, .PivotField.Name)
End If
sc.Slicers.Add SlicerDestination:=ActiveSheet, Top:=rngDest.Top, Left:=rngDest.Left
End With
If Err.Number > 0 Then 'SlicerCache already existed. Work out what it's index is
On Error GoTo 0
For Each sc In ActiveWorkbook.SlicerCaches
For Each ptOther In sc.PivotTables
If ptOther = pt Then
bFoundCache = True
Exit For
End If
Next ptOther
If bFoundCache Then Exit For
Next sc
End If
varAnswer = MsgBox(Prompt:="Make Slicer control the " & pf.Name & " field in all Pivots on the same sheet?", Buttons:=vbYesNo)
If varAnswer = vbYes Then
For Each ptOther In ActiveSheet.PivotTables
If ptOther.CacheIndex = pt.CacheIndex And ptOther.Parent.Name = pt.Parent.Name Then
sc.PivotTables.AddPivotTable ptOther
End If
Next
End If
Else: MsgBox "You can't add a Slicer to a Values field."
End If
End Sub
In addition, the below code will add the Add Slicer icon to the right-click menu that comes up when you right click on a PivotField:
Option Explicit
Private Sub Workbook_Open()
AddShortcuts
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteShortcuts
End Sub
Sub AddShortcuts()
Dim cbr As CommandBar
DeleteShortcuts
Set cbr = Application.CommandBars("PivotTable Context Menu")
With cbr.Controls.Add(Type:=msoControlButton, Temporary:=True)
.Caption = "Add Slicer"
.Tag = "AddSlicer"
.OnAction = "AddSlicer"
.Style = msoButtonIconAndCaption
.Picture = Application.CommandBars.GetImageMso("SlicerInsert", 16, 16)
End With
End Sub
Sub DeleteShortcuts()
Dim cbr As CommandBar
Dim ctrl As CommandBarControl
Set cbr = Application.CommandBars("PivotTable Context Menu")
For Each ctrl In cbr.Controls
Select Case ctrl.Tag
Case "AddSlicer"
ctrl.Delete
End Select
Next ctrl
End Sub
…meaning whenever I right click on a PivotField I get this:
Clicking on that adds a Slicer to the selected field automatically, plus asks you:
Hell yes, I do!
Here’s a sample file:
Sync-PivotTables-from-dropdown_20140818
snb commented over at the original forum thread that he reworked my code. Here’s snb’s much shorter revision :
And here’s how he adds the right click option:
..to:
...because the commandbar indexes vary between Excel 2010 and 2013 as per further comments below.
In Excel 2010 you should use: commandbars(51)
Private Sub Workbook_Open()
With Application.CommandBars(51).Controls.Add(1)
.OnAction = "AddSlicer"
.Caption = "Add Slicer"
.Picture = Application.CommandBars.GetImageMso("SlicerInsert", 16, 16)
End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars(51).Controls("Add Slicer").Delete
End Sub
I wonder if Application.CommandBars(“PivotTable Context Menu”) is the same in all different language versions of Excel. I certainly wouldn’t recommend going down the Index number path, given what we’ve learned above. It seems crazy to me that the index numbers would vary across versions.
snb: Note that your code won’t let users add a new slicer to another PivotTable for the same field. Slicers only share the same cache if they are connected to the exact same PivotTable(s). Otherwise they have different caches, and the names have a pretty bizarre naming convention:
This means you can’t rely on this line:
Not that I would want to, because who knows whether MS will change how they assign names to Slicers in the next version.
Wouldn’t that be repaired by:
Sub M_snb()
On Error Resume Next
If ActiveCell.PivotField.Orientation <> 4 Then
If Err.Number <> 0 Then Exit Sub
c00 = ActiveCell.PivotField.Name
If c00 = "" Then c00 = ActiveCell.PivotField.CubeField.Name
c01 = "SL_" & ActiveCell.PivotTable.Name & c00
c02 = ActiveWorkbook.SlicerCaches(c01).Index
If c02 = "" Then ActiveWorkbook.SlicerCaches.Add(ActiveCell.PivotTable, c00, c01).Slicers.Add ActiveSheet
If MsgBox("Make Slicer control the " & c00 & " field in all Pivots on the same sheet?", 4) = 6 Then
For Each pt In ActiveSheet.PivotTables
If pt.CacheIndex = ActiveCell.PivotTable.CacheIndex Then ActiveWorkbook.SlicerCaches(c01).PivotTables.AddPivotTable pt
Next
End If
End If
End Sub
> If MsgBox(“Make Slicer control the ” & c00 & ” field in all Pivots on the same sheet?”, 4) = 6 Then
There is a reason for VBA constants: they make your code legible.
Yeah, I agree. I’m always more interested in seeing different approaches that a shorthand reworking.
Hi,
This is really helpful code. Thank-you for posting. Could you also please explain how would this code could be modified to add ALL pivot tables in an entire workbook, not just the active worksheet, as report connections to the newly created slicer?
Thank-you!
Hi SL. I’ve got some revised code that lets you choose whether you want to connect the Slicer to all Pivots in the activesheet, or to all pivots in the active workbook. I’ll send you an email with the code.
Hello. Great info. So is the code from Aug 16th & 17th the right version to use for excel 2013 or does it need further modifications? It’s been awhile and I’m pretty rusty.
thanks!!
Jeff Weir, I’m just finding this code on Google and wondering if I could get a copy of your code that syncs a slicer to All Pivots in the active sheet or active workbook?
RE:I’ve got some revised code that lets you choose whether you want to connect the Slicer to all Pivots in the activesheet, or to all pivots in the active workbook. I’ll send you an email with the code.
You saved my life