Home > Macros > Basics > Slicers Update Excel Slicers with MacroSlicers were introduced in Excel 2010, and they make it easy to change multiple pivot tables with a single click. In Excel 2013 and later versions, you can also use slicers on formatted Excel tables. You can use programming to control the Excel Slicers, and this page has sample code that you can use. When you select an item in one slicer, it will automatically select the same item in another slicer. |
Update Multiple Pivot Table SlicersIn 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 |
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
Update Multiple Excel Table SlicersIn the Download section, there is another Slicer Macro workbook that you can download, and it has two different Excel tables with sales data.
Two Excel Tables and SlicersIn the workbook, each Excel table is on a separate sheet, with a Region Slicer at the top left corner of the sheet.
|
Admin Sheet with Macro CodeOn 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. Admin Sheet CodeOn 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 |
Macro Code to Update SlicersHere 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 |
Video: Connect Slicer to Multiple Pivot TablesIn 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. |
Download the Sample Files
|
More Pivot Table Resources |
Last updated: March 3, 2023 10:45 AM