VBA – Automated Pivot Filtering

Pivot tables are one of the most powerful tools in Excel for summarizing and analyzing data. No doubt about it. However, sometimes you need to go beyond their built-in functionality. In this article, we will explore how to use VBA to dynamically filter a pivot table based on user selections from a slicer. This technique is particularly useful when you want to sync filters across multiple pivot tables or automate repetitive tasks. It can actually save hours of work and days of explanations.

We will use a real-world example involving English football (soccer) teams and their trophies over the last 11 years. So, imagine the following nice task in 3 points:

1. Allow users to select teams using a slicer.
2. Record the selected teams in a temporary table.
3. Filter a second pivot table to show only the selected teams.

This process can be tedious if done manually, but with VBA, it becomes quick and efficient. The proposed solution in the article (and also implemented in the YouTube video) is the following:

1. Extracting Selected Slicer Items: Record the selected teams in a temporary table.
2. Filtering the Pivot Table: Dynamically filter the pivot table based on the recorded selections.
3. Helper Functions: Ensure the code is robust and error-free.

The code and the explanations are below:

Sub ListSelectedSlicerItems()

    Dim slCache As SlicerCache
    Dim si As SlicerItem
    Dim selectedItems As String
    Dim outputRange As Range

    Set slCache = ThisWorkbook.SlicerCaches("Slicer_Team")
    
    Dim a As Long: a = 1
    Dim b As Long: b = 1
    
    tblTemp.Cells.Delete
    
    For Each si In slCache.SlicerItems
        If si.Selected Then
            tblTemp.Cells(a, "A") = si.Name
            tblTemp.Cells(a, "A").Interior.Color = vbGreen
            a = a + 1
        Else
            tblTemp.Cells(b, "B") = si.Name
            tblTemp.Cells(b, "B").Interior.Color = vbYellow
            b = b + 1
        End If
    Next si
    
    tblTemp.Cells.EntireColumn.AutoFit

End Sub

How It Works

• The code loops through each item in the slicer (Slicer_Team).
• Selected items are recorded in Column A of the tblTemp sheet and highlighted in green.
• Unselected items are recorded in Column B and highlighted in yellow.
• The temporary table is cleared before each run to ensure fresh data.
• Once the selected teams are recorded, we go to the next step, filtering the pivot dynamically

Sub FilterPivotTableBasedOnSelectedTeams()

    Dim pt As PivotTable
    Dim selectedItemsRange As Range
    Dim myCell As Range
    Dim fieldName As String
    Dim lastRowSelected As Long
    Dim pi As PivotItem
    Dim firstItemSet As Boolean

    Set pt = ThisWorkbook.Worksheets("PivotTable2").PivotTables("PivotTable2")
    lastRowSelected = LastRow(tblTemp.Name, 1)
    Set selectedItemsRange = tblTemp.Range("A1:A" & lastRowSelected)
    fieldName = "Team"
    pt.PivotFields(fieldName).ClearAllFilters
    
    Dim itemsTotal As Long
    itemsTotal = pt.PivotFields(fieldName).PivotItems.Count
    
    For Each pi In pt.PivotFields(fieldName).PivotItems
        If Not IsInRange(pi.Name, selectedItemsRange) Then
            itemsTotal = itemsTotal - 1
            If itemsTotal = 0 Then
                Err.Raise 222, Description:="No value in the pivot!"
                Exit Sub
            End If
            
            pi.Visible = False
        End If
    Next pi

End Sub

Function IsInRange(myValue As String, myRange As Range) As Boolean
    
    Dim myCell As Range
    IsInRange = False
    For Each myCell In myRange.Cells
        If myCell.value = myValue Then
            IsInRange = True
            Exit Function
        End If
    Next myCell

End Function

Public Function LastRow(wsName As String, Optional columnToCheck As Long = 1) As Long

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(wsName)
    LastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row

End Function

How They Work

• IsInRange: Loops through a range and checks if a specific value exists.
• LastRow: Finds the last non-empty row in a specified column.

The whole Excel file is in GitHub and the YouTube video is below!

Enjoy it!