How to link multiple PivotTables to a single slicer using VBA?

Linking multiple PivotTables to a single slicer using VBA in Excel can be a powerful way to easily filter data across multiple PivotTables simultaneously. Here is a step-by-step guide on how to achieve this using VBA:

Step-by-Step Guide

    Sub ConnectSlicerToPivots()
        Dim ws As Worksheet
        Dim slicer As Slicer
        Dim pt As PivotTable
        Dim i As Integer

        ' Change "Sheet1" and "Slicer1" to your worksheet name and slicer name
        Set ws = ThisWorkbook.Sheets("Sheet1")
        Set slicer = ws.Slicers("Slicer1")

        ' Loop through each PivotTable in the Worksheet
        For i = 1 To ws.PivotTables.Count
            Set pt = ws.PivotTables(i)
            If pt.Name <> slicer.PivotTables(1).Name Then
                slicer.PivotTables.AddPivotTable pt
            End If
        Next i

        MsgBox "Slicer connected to all PivotTables on the worksheet!"

    End Sub

Set Up Your PivotTables:

Ensure you have multiple PivotTables set up in your Excel workbook. All PivotTables should ideally be connected to the same data source for the slicer to sync correctly across them.

Insert a Slicer Manually:

Go to one of your PivotTables, select it, and then navigate to the `PivotTable Analyze` (or `Options` in older versions) tab.
Click on `Insert Slicer` and choose the field that you want to use for filtering.

Initialize the Slicer Connection:

After inserting the slicer, it will only be connected to one PivotTable. We can now use VBA to connect it to the others.

Open the VBA Editor:

Press `ALT + F11` to open the VBA Editor.

Insert a New Module:

In the VBA Editor, go to `Insert > Module` to create a new module.

Write the VBA Code:

Copy and paste the following code into the module window. This example assumes that your slicer is connected to the first PivotTable, and you want to connect it to others too.
Modify the Code:
Make sure to replace `”Sheet1″` with the name of your worksheet, and `”Slicer1″` with the actual name of your slicer. You can find your slicer’s name by selecting it, and checking the `Slicer` tab on the Ribbon.

Run the VBA Code:

Close the VBA Editor and go back to Excel.
Press `ALT + F8`, select `ConnectSlicerToPivots`, and click `Run`.

Notes:

Ensure all PivotTables lie within the bounds of the slicer’s PivotCache source; otherwise, they will not sync correctly.
If you rearrange data or change data sources, you might need to redo the steps.
Be cautious when running macros, particularly on sensitive data, and ensure macros are allowed to run in your Excel Trust Center settings.

This approach allows linking a slicer to all PivotTables on the specified worksheet, enabling consistent data views across your PivotTables with a single click on the slicer.

facebooklinkedin