Create a dynamic filter for a PivotTable with excel VBA

Goal:

We want to create a dynamic filter that updates automatically based on the unique values from a specific column in the source data. For example, let’s say you have a dataset with a « Region » column and want to create a PivotTable that allows the user to dynamically filter by Region.

Steps:

1. Prepare Your Data: Ensure your source data is organized as a table (not a simple range). This makes it easier for PivotTables and filtering.

Example:

1. Create the Pivot Table: Manually create a PivotTable, or use VBA to create it. In this example, let’s assume the PivotTable will be created in a new worksheet.
2. Dynamic Filter: We will write VBA code to automatically create a filter for the PivotTable based on the unique values from the “Region” column.

VBA Code:

Sub CreateDynamicFilterForPivotTable()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pRange As Range
    Dim filterField As PivotField
    Dim sourceData As Range
    Dim uniqueRegions As Collection
    Dim region As Variant
    Dim i As Long
    ' Set the worksheet and range of source data
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
    Set sourceData = ws.Range("A1:C5") ' Adjust to your actual data range
    ' Create Pivot Table
    Set pRange = ws.Range("E1") ' Top-left cell where Pivot Table will be placed
    Set pt = ws.PivotTableWizard(SourceType:=xlDatabase, SourceData:=sourceData, TableDestination:=pRange)
    ' Add fields to the Pivot Table (adjust accordingly)
    With pt
        .PivotFields("Region").Orientation = xlPageField
        .PivotFields("Sales").Orientation = xlDataField
        .PivotFields("Date").Orientation = xlRowField
    End With
    ' Create a collection to hold unique regions
    Set uniqueRegions = New Collection
    ' Loop through the source data to get unique regions
    On Error Resume Next ' Ignore errors when adding duplicates
    For i = 2 To sourceData.Rows.Count ' Skip header row
        uniqueRegions.Add sourceData.Cells(i, 2).Value, CStr(sourceData.Cells(i, 2).Value)
    Next i
    On Error GoTo 0 ' Turn back on regular error handling
    ' Set the Pivot Field for Region
    Set filterField = pt.PivotFields("Region")
    ' Clear existing filters
    filterField.ClearAllFilters
    ' Loop through unique regions and apply as dynamic filter
    filterField.EnableMultiplePageItems = True
    For Each region In uniqueRegions
        filterField.PivotItems(region).Visible = True
    Next region
    ' Optional: Apply an initial filter (e.g., first region)
    filterField.CurrentPage = uniqueRegions(1)
    MsgBox "Dynamic filter for PivotTable created successfully!"
End Sub

Explanation of the Code:

1. Setting Variables:
   • We define variables for the worksheet (ws), the PivotTable (pt), the source data (sourceData), and other necessary elements like the filter field and unique regions.
2. Source Data Range: The range sourceData holds the data that we’ll use to build the PivotTable. You can adjust this range to fit your dataset.
3. Creating the Pivot Table: The PivotTableWizard method is used to create a new PivotTable. We specify the source data and the destination for the PivotTable. Then we add the fields to the PivotTable:
   • « Region » is added as a filter field (i.e., for dynamic filtering),
   • « Sales » is added as a data field,
   • « Date » is added as a row field.
4. Unique Values Collection: A Collection is used to store unique values from the « Region » column. This ensures that only distinct values are added to the filter.
5. Clearing Filters: Before applying new filters, we clear any existing filters with ClearAllFilters.
6. Applying Dynamic Filters: We loop through the collection of unique regions and apply them as filters on the PivotTable. If you want multiple filter options, the EnableMultiplePageItems property allows it.
7. Initial Filter: Optionally, you can set an initial filter by setting CurrentPage to a specific region (e.g., the first region in the collection).
8. Final Message: After applying the dynamic filter, a message box notifies the user that the process is complete.

Notes:

• Ensure that the PivotTable is correctly created and that your data range is dynamic. You can replace the hardcoded ranges with dynamic ranges if needed.
• The code applies the filter directly to the PivotTable on the “Region” field. You can modify the logic if you need more fields or filters.
• To improve the user experience, consider adding error handling, especially when the PivotTable already exists, or the data range is not set correctly.

This code should work dynamically to update the filter options on your PivotTable based on the unique values from your data.