How to filter a PivotTable using a slicer with VBA?

To filter a PivotTable using a slicer with VBA, you need to ensure that you have a PivotTable and a Slicer connected to that PivotTable in your Excel workbook. Here’s a step-by-step guide to accomplish this task using VBA:

Sub FilterPivotTableUsingSlicer()

    Dim ws As Worksheet
    Dim pvt As PivotTable
    Dim slc As Slicer
    Dim slcCache As SlicerCache
    Dim slcItem As SlicerItem
    
    ' Set your worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
    
    ' Set your PivotTable
    Set pvt = ws.PivotTables("PivotTable1") ' Change to your PivotTable name
    
    ' Set your Slicer
    Set slcCache = ThisWorkbook.SlicerCaches("Slicer_SomeFieldName") ' Change to your Slicer name
    
    ' For example, clear previous filters
    slcCache.ClearManualFilter
    
    ' Loop through Slicer Items and filter based on specific criteria
    For Each slcItem In slcCache.SlicerItems
        If slcItem.Name = "FilterValue" Then ' Change "FilterValue" to the value you want
            slcItem.Selected = True
        Else
            slcItem.Selected = False
        End If
    Next slcItem

End Sub

Create a Pivot Table and a Slicer Manually:

First, create your PivotTable using your data set.
Then, insert a Slicer for one of the fields in the PivotTable for which you want to filter.

Find the Slicer Name:

Click on the Slicer. In the Excel Ribbon, go to the Slicer tab and note the name of the Slicer. It will be something like `Slicer_SomeFieldName`.

Write VBA Code to Filter the Pivot Table:

Open the VBA editor by pressing `ALT + F11`.
Insert a new module to write your VBA code by clicking `Insert > Module`.
Use the following VBA code as a reference or a starting point to filter the Pivot Table using the Slicer:

Adjust the VBA Code:

Replace `”Sheet1″` with the name of your worksheet.
Replace `”PivotTable1″` with the name of your PivotTable.
Replace `”Slicer_SomeFieldName”` with the actual name of your slicer.
Replace `”FilterValue”` with the value you want to filter in the PivotTable.

Run the Code:

Close the VBA editor and return to Excel.
Run your macro by pressing `ALT + F8`, select your macro `FilterPivotTableUsingSlicer`, and click `Run`.

This VBA script sets the slicer to filter the PivotTable to display only data associated with a specific value. Adjust `”FilterValue”` to control which item is selected in the slicer. You can also extend the VBA logic to handle multiple selections or implement more complex filter criteria.

facebooklinkedin