Sync Pivots from dropdown

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

12 thoughts on “Sync Pivots from dropdown”

snb commented over at the original forum thread that he reworked my code. Here’s snb’s much shorter revision :

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 = ActiveWorkbook.SlicerCaches("SL_" & c00).Index
        If c01 = "" Then ActiveWorkbook.SlicerCaches.Add(ActiveCell.PivotTable, c00, "SL_" & c00).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("SL_" & c00).PivotTables.AddPivotTable pt
            Next
        End If
    End If
End Sub

And here’s how he adds the right click option:

Private Sub Workbook_Open()
    With Application.CommandBars("PivotTable Context Menu").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("PivotTable Context Menu").Controls("Add Slicer").Delete
End Sub


Edit: Have changed the line:

With Application.CommandBars(60).Controls.Add(1)

..to:

With Application.CommandBars("PivotTable Context Menu").Controls.Add(1)

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

The first one gets named SL_SomeFieldName
The second one gets named Slicer_SomeFieldName
The third one gets named Slicer_SomeFieldName1

This means you can’t rely on this line:

c01 = ActiveWorkbook.SlicerCaches("SL_" & c00).Index

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.