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

9 thoughts on “Sync Pivots from dropdown

  1. 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. 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. 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. 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. 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. 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!

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

Leave a Reply

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

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax