|
Home > Macros > Basics > Slicers Update Excel Slicers with Macro
|

In an Excel workbook, some pivot tables might be based on the same pivotcache, and other pivot tables are based on different pivot caches.
In the steps below, see how to set up a workaround, to automatically update slicers for pivot tables that have different data sources, when one of those slicers changes.
-- 1) Workbook Setup
-- 2) Create Short List of Cities
-- 3) Build a Pivot Table
-- 4) Add a Slicer
-- 5) Add Worksheet Code
-- 6) Move the Slicers
NOTE: You can download a copy of this example, in the Download section at the bottom of this page
In this workbook example:

In the sales data used for the pivot table, there are sales records from four different cities: Boston, Los Angeles, New York, and San Diego.
As a way to show specific cities only, we'll use this workaround:
Follow these steps to set up the short list of cities.

If you are using Excel 2013, you can use a slicer on an Excel table. In Excel 2010 and 2007, you'll have to build a pivot table, based on the city list, before you can create the slicer.
Follow these steps to build a pivot table.


The next step is to add a slicer. In Excel 2013, you can connect the slicer directly to the table, and in Excel 2007 or 2010, use the pivot table.
Follow these steps to add the slicer:

After the Slicer is added to the worksheet, you can change its settings, if needed, and test the Slicer.
To see the slicer's "formula" name, so you can add that to the macro, follow these steps:

Next, to test the slicer, follow these steps:

The next step is to add Excel VBA code to the worksheet module, for the sheet where the short city list is stored.
Private Sub Worksheet_PivotTableUpdate _
(ByVal Target As PivotTable)
Dim wb As Workbook
Dim scShort As SlicerCache
Dim scLong As SlicerCache
Dim siShort As SlicerItem
Dim siLong As SlicerItem
On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
Set wb = ThisWorkbook
Set scShort = wb.SlicerCaches("Slicer_City")
Set scLong = wb.SlicerCaches("Slicer_City1")
scLong.ClearManualFilter
For Each siLong In scLong.VisibleSlicerItems
Set siLong = scLong.SlicerItems(siLong.Name)
Set siShort = Nothing
On Error Resume Next
Set siShort = scShort.SlicerItems(siLong.Name)
On Error GoTo errHandler
If Not siShort Is Nothing Then
If siShort.Selected = True Then
siLong.Selected = True
ElseIf siShort.Selected = False Then
siLong.Selected = False
End If
Else
siLong.Selected = False
End If
Next siLong
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox "Could not update pivot table"
Resume exitHandler
End Sub
The final step is to move the slicers -- the short list slicer will go onto the main sheet, beside the original pivot table. The long slicer will go onto the short list sheet, where it will be updated by the code.
To move a slicer:
After moving both the slicers, click on a city in the short city slicer, and the main pivot table should change, to show only the results for that city. If you check the slicer on the short list sheet, it should have changed, to show the same city (or cities) selected.

In the Download section, there is another Slicer Macro workbook that you can download, and it has two different Excel tables with sales data.
In the workbook, each Excel table is on a separate sheet, with a Region Slicer at the top left corner of the sheet.

On another sheet, named Admin, there is a Slicer for each sales data table, showing the Region names.

On the Admin, there is also a small pivot table for each sales data table. There is only one field in the layout for each pivot table - the Region field, in the Row area.
Note: The Slicers at the top left of the Sales Data sheets are connected to those small pivot tables.

On the Admin sheet code module, there is code that runs automatically, when one of those small pivot tables is updated -- for example, if you click a Slicer to select a Region name.
Here is the code -- it gets the name of the pivot table that was updated, then runs a macro named UpdateSlicers. You can see that code in the next section, and in the sample file.
Private Sub Worksheet_PivotTableUpdate _ (ByVal Target As PivotTable) strPT = Target.Name UpdateSlicers End Sub
Here is the code for the UpdateSlicers macro, which is stored on a regular code module, named modSlicers, in the sample file.
Option Explicit
Global strPT As String
'----------------------
Sub UpdateSlicers()
Dim wb As Workbook
Dim pt As PivotTable
Dim scMain As SlicerCache
Dim sc As SlicerCache
Dim siMain As SlicerItem
Dim si As SlicerItem
On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
Set wb = ThisWorkbook
Set pt = Sheets("Admin").PivotTables(strPT)
Set scMain = pt.Slicers(1).SlicerCache
For Each sc In wb.SlicerCaches
If sc.Name <> scMain.Name Then
sc.ClearManualFilter
End If
For Each si In sc.VisibleSlicerItems
Set si = sc.SlicerItems(si.Name)
Set siMain = Nothing
On Error Resume Next
Set siMain = scMain.SlicerItems(si.Name)
On Error GoTo errHandler
If Not siMain Is Nothing Then
If siMain.Selected = True Then
si.Selected = True
ElseIf siMain.Selected = False Then
si.Selected = False
End If
Else
si.Selected = False
End If
Next si
Next sc
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox "Could not filter all tables"
Resume exitHandler
End Sub
In this video, you'll see the steps for connecting multiple pivot tables to a slicer, so they can all be filtered with a single click.
Use this technique, if all pivot tables are based on the same data source.
The written instructions are on the Pivot Table Slicers page.
Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.
Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!
Clear Old Items in Pivot Table
Last updated: September 17, 2024 1:10 PM