How to filter a PivotTable based on external data using VBA?

Filtering a PivotTable based on external data using VBA requires a few steps. Below is a guide to help you accomplish this task:

Steps to Filter a PivotTable Using VBA:

Set Up Your Data:

Ensure your external data (to be used for filtering) is accessible, for example, in a separate worksheet or even in an external file like CSV or Excel file.

Open the VBA Editor:

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

Access the Correct Workbook and Worksheet:

Make sure you’re working in the correct workbook and accessing the appropriate worksheet where your PivotTable is located.

Write the VBA Code:

Here is an example that demonstrates how to filter a PivotTable using external data contained in another worksheet within the same workbook:

   Sub FilterPivotTable()
       Dim wb As Workbook
       Dim ws As Worksheet
       Dim pt As PivotTable
       Dim pf As PivotField
       Dim filterValues As Range
       Dim cell As Range
       Dim filterArray() As Variant
       Dim i As Long

       ' Set your workbook and worksheet
       Set wb = ThisWorkbook
       Set ws = wb.Sheets("PivotTableSheet") ' Change this to your pivot table sheet name

       ' Set your Pivot Table
       Set pt = ws.PivotTables("PivotTable1") ' Change this to your pivot table name

       ' Set the pivot field to filter (e.g., "Category")
       Set pf = pt.PivotFields("Category") ' Change this to your field name

       ' Set the range of your external data for filtering
       ' Assume this is on a sheet named "ExternalData" in column A
       Set filterValues = wb.Sheets("ExternalData").Range("A1:A10") ' Change this range as necessary

       ' Turn off any existing filters
       pf.ClearAllFilters

       ' Loop through the external data and create a filter criteria array
       ReDim filterArray(1 To filterValues.Cells.Count)
       i = 1
       For Each cell In filterValues
           filterArray(i) = cell.Value
           i = i + 1
       Next cell

       ' Apply the filter to the PivotTable
       pf.EnableMultiplePageItems = True
       pf.CurrentPage = "(All)"
       pf.PivotItems("(All)").Visible = False

       For i = LBound(filterArray) To UBound(filterArray)
           On Error Resume Next ' In case the item is not found
           pf.PivotItems(filterArray(i)).Visible = True
           On Error GoTo 0
       Next i

       ' Refresh the PivotTable to apply the filter
       pt.RefreshTable

   End Sub

Important Points to Note:

Update Placeholder Values:

Change `”PivotTableSheet”`, `”PivotTable1″`, and `”Category”` to your actual sheet names, PivotTable name, and field name.

Error Handling:

The script currently uses `On Error Resume Next` to handle errors gracefully in case some items do not exist in the PivotTable. This should be handled according to your needs.

Multiple Page Items:

`pf.EnableMultiplePageItems = True` allows for filtering multiple items.

Dynamic Range:

Ensure your range `filterValues` accurately represents the range of data used for filtering. You might also use other methods like defining a named range for flexibility.

By following these steps, you can filter a PivotTable using data from another source in Excel using VBA.

facebooklinkedin