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:

Two Pivots and target

 
 

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

Faux DV and Fields List

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:

Faux DV and Pivots

 
 

From the ANALYZE tab of the PivotTable Tools contextual menu in the ribbon, click the Insert Slicer icon:

Insert Slicer

 
 

…and from the menu that comes up, choose the field name that matches the field you put in the Master Pivot:

Chosen field

 
 
…and your slicer will magically appear:

Slicer added

 
 

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:

Right Click

 
 

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:

Report Connections Master

 
 

What we want to do is connect it to the other PivotTables, by checking those other checkboxes:

SlicerConnections_AllControlled

 
 

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

Dont select multiple items

 
 

…and now all we need to do is move that Slicer somewhere out of sight (but don’t delete it):

Faux DV and Pivots

 
 

Now when we select a region from that Master Pivot dropdown…

Select Region

 
 
… all the other Pivots are filtered to match:

PivotsFiltered

 
 

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:

Just Use Slicer

 
 

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:

Multiple Dropdowns

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:

AddSlicer

 
 

Clicking on that adds a Slicer to the selected field automatically, plus asks you:

Control all pivots

 
 

Hell yes, I do!

Here’s a sample file:
Sync-PivotTables-from-dropdown_20140818

 
 

Nightmare

7 Comments

  1. Jeff Weir says:

    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.

  2. snb says:

    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
  3. Jeff Weir says:

    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.

  4. Jeff Weir says:

    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.

  5. snb says:

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

  7. Jeff Weir says:

    Yeah, I agree. I’m always more interested in seeing different approaches that a shorthand reworking.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: