How to add a slicer to a PivotTable using VBA?

To add a slicer to a PivotTable using VBA, you first need to ensure that your Excel workbook contains a PivotTable. Then, you can use VBA to create a slicer for that PivotTable. Below is a step-by-step guide including a sample VBA code:

Step-by-Step Guide:

Sub AddSlicerToPivotTable()

    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim slicerCache As SlicerCache
    Dim slicer As Slicer

    ' Set reference to the worksheet containing the PivotTable
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Change Sheet1 to your sheet's name

    ' Set reference to the PivotTable you want to add a slicer to
    Set pt = ws.PivotTables("PivotTable1") ' Change PivotTable1 to your PivotTable's name

    ' Add a slicer for a specific PivotField
    Set slicerCache = ThisWorkbook.SlicerCaches.Add(pt, "FieldName") ' Change FieldName to the desired field in your PivotTable

    ' Adds slicer to worksheet
    Set slicer = slicerCache.Slicers.Add(ws, , "Slicer Name", "Slicer Name", 100, 100, 200, 200)

    ' Optional: Format the slicer as desired
    slicer.Style = "SlicerStyleLight1" ' You can choose a different style

End Sub

Open the Visual Basic for Applications (VBA) Editor:

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

Insert a New Module:

In the VBA editor, right-click on any existing module or the `VBAProject` where you want to add the code.
Select `Insert` > `Module` to create a new module.

Write the VBA Code:

Copy the following VBA code into the module. Be sure to adjust the parameters such as worksheet names and PivotTable names to match your specific Excel workbook setup.

Important Notes:

Adjust Worksheet and PivotTable Names: Replace `”Sheet1″` and `”PivotTable1″` with the actual worksheet and PivotTable names in your Excel file.
Field Name: Replace `”FieldName”` with the actual field name in your PivotTable you want the slicer to filter.
Slicer Position and Size: The parameters `(100, 100, 200, 200)` represent the slicer’s position and size (left, top, width, height). Adjust them as needed for your layout.
Slicer Style: Change `”SlicerStyleLight1″` to another style if you prefer a different look.
Run the Macro:

You can run the macro by pressing `F5` within the VBA editor or by assigning the macro to a button in Excel.

This script will add a slicer to your specified PivotTable, allowing you to easily filter the data according to the field you specified. Adjust the code as necessary to fit your specific scenario.

facebooklinkedin