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:
- Allow users to select teams using a slicer.
- Record the selected teams in a temporary table.
- Filter a second pivot table to show only the selected teams.
Filtering is based on the football (soccer) teams that won an English Premier League in the last 11 years.
This is the second table, that is actually being filtered, based on the first one.
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:
- Extracting Selected Slicer Items: Record the selected teams in a temporary table.
- Filtering the Pivot Table: Dynamically filter the pivot table based on the recorded selections.
- Helper Functions: Ensure the code is robust and error-free.
The code and the explanations are below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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
tblTempsheet 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
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!