Update Excel Slicers with Macro

Slicers were introduced in Excel 2010, and they make it easy to change multiple pivot tables with a single click. In Excel 2013, you can also use slicers on formatted Excel tables.

You can use programming to control the 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.

Workbook Setup

If multiple pivot tables are based on the same data source, you can create a slicer, and connect it to one or more of those pivot tables. However, if there are pivot tables from different data sources, you can't connect the to the same slicer.

In this example, there is a pivot table based on a table with sales data, and a slicer for the City field.

The source data has four cities, and we only want Boston and Los Angeles available in the slicer The slicer shows all the items, and we can't hide the cities that we don't want to use.

Instead, we'll create another pivot table, with just the short list of cities, and use programming to change the selections in the main slicer.

Create a Short List of Cities

Follow these steps to set up the short list of cities.

1. On a blank worksheet, type the heading, City, and a list with the two cities, Boston and Los Angeles.
2. Select a cell in the list, and on the Home tab of the Ribbon, click Format as Table
3. .Click on a Table Style
4. Check the data range, and add a check mark to "My table has headers"
5. Click OK, to confirm that you want to create a table.

Build a Pivot Table

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.

1. Select a cell in the short list of cities.
2. On the Insert tab of the Ribbon, click PivotTable
3. Select "Existing worksheet" and click in the Location box
4. Select a cell on the same worksheet as the list, and click OK

   

5. In the pivot table field list, add a check mark to City, to add it to the pivot table, in the Row area

   

Add a Slicer

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:

1. Select a cell in the pivot table (2007/2010) or the table (2013).
2. On the Ribbon's Insert tab, click Slicer
3. In the Slicer window, add a check mark to City, and click OK

   

4. To see the slicer's name, select the slicer, and click Slicer Settings on the Slicer, Options Ribbon tab. You will use that name in the code

   

5. To test the slicer, click on one of the city names, and the pivot table will only show that city.
6. To clear the filter, click the Clear Filter button, at the top right of the slicer

   

Add the Worksheet Code

The next step is to add code to the worksheet module, for the sheet where the short city list is stored.

1. Right-click on the sheet tab for the city list sheet, and click View Code
2. Copy the code below, and paste it onto the sheet's code module.
3. Change the slicer names, to match the slicer names in your file (see the slicer name in Slicer Settings)
4. Save the file as macro-enabled.

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

Move the Slicers

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:

1. Right-click on the slicer, and click Cut
2. Select the sheet where you want to put the slicer
3. Right-click on the worksheet, and click Paste

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.

Download the Sample File

1. To download the completed sample file, click here. The zipped file contains macros, so enable macros to test the slicers.

2. Download a completed sample file that has two sets of Slicers (one for City, one for Category). The zipped file contains macros, so enable macros to test the slicers.

3. Download a sample file with Table Slicers (2 Excel Tables). Each table has a pivot table, with a Slicer. Pivot Slicers are on the table sheets, and when clicked, all the Slicers are updated, and the 2 tables are filtered. The zipped file contains macros, so enable macros to test the slicers.

More Pivot Table Resources

• FAQs - Pivot Tables
• Pivot Table Introduction
• Grouping Data
• Multiple Consolidation Ranges
• Running Totals
• Summary Functions
• Clear Old Items in Pivot Table