VBA: Update a PivotTable Filter with Code

To update a PivotTable filter with VBA, reference the PivotTable and its field, clear any existing filter, then set the desired item. For a single-select report filter named Region, for example, use pf.CurrentPage = "East" after assigning pf to that PivotField; the examples below show the full setup and alternatives.

What “updating a filter” means

A PivotTable filter controls which source records contribute to the displayed summary. In Excel, a filter can appear in several places: as a report filter above the table, as a dropdown on a row or column label, or as a value, label, or date filter. VBA uses different properties for these jobs, so the first step is identifying how the field is being used.

Changing a filter is also different from refreshing a PivotTable. A filter selects among items already known to the PivotTable; a refresh asks Excel to update its cache from the source data. If the source has gained new rows or categories, refresh first so the new items are available to select. If you only want to switch from East to West, changing the filter may be enough.

In the examples, the worksheet is named Summary, the PivotTable is named ptSales, and the field is Region. Replace those names and the item text with the names and captions in your own workbook. PivotTable names are visible when you select the table, on the PivotTable Analyze tab in the PivotTable Name box.

Choose the right kind of PivotTable filter

Report filter, one item: Set the field’s CurrentPage property. This is usually the clearest option for selecting one region, year, or department.
Report filter, several items: Enable multiple page items and set the visibility of the PivotItems you want included.
Row or column label filter: Set item visibility or use a PivotField filter, taking care not to hide every item.
Value, label, or date condition: Use the appropriate PivotField filter method and criteria. These are not equivalent to selecting one item with CurrentPage.
Timeline or slicer: These are separate filter controls. Their selection is generally managed through slicer or timeline objects rather than by assigning a page field’s CurrentPage.

For a report filter, the field must be in the report-filter area, represented in VBA by xlPageField. If the field is currently a row label, assigning CurrentPage is not the right fix: move it to the report-filter area in the PivotTable layout, or use a row-field filtering approach instead.

Prepare the workbook and identify the objects

Use the exact worksheet, PivotTable, field, and item captions. These names are not interchangeable: the PivotTable name is not its worksheet name, and a field caption such as “Region” may differ from the source column’s internal name. Item text must also match the item shown in the filter, including spaces and punctuation.

On Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, and put the procedure there. In Excel for Mac, open the editor with Option+F11; some keyboards require Fn+Option+F11, and the Developer tab can also open Visual Basic. VBA is not available for running macros in Excel for the web.

Before running any macro, save a copy of the workbook. Macros cannot be undone with Ctrl+Z. If the file contains VBA, save it as an Excel Macro-Enabled Workbook (.xlsm), and allow macros only when you trust the workbook and its code.

Set one report-filter item

This example refreshes the PivotTable, clears the Region field’s old filter, and selects East. It assumes Region is already a report filter and that East exists among its items. Refreshing first is useful when the source has changed, but it is not necessary for every simple filter change.

Sub SetRegionFilter()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField

    ' Use the worksheet, PivotTable, and field names in this workbook.
    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("ptSales")
    Set pf = pt.PivotFields("Region")

    ' Update the cache-backed PivotTable before choosing an item.
    pt.RefreshTable

    ' Remove the previous selection and choose one report-filter item.
    pf.ClearAllFilters
    pf.EnableMultiplePageItems = False
    pf.CurrentPage = "East"
End Sub

ClearAllFilters removes the field’s existing filter; it does not delete the PivotTable or its source data. Setting EnableMultiplePageItems to False makes the single-selection intention explicit for a report filter. If “East” is missing or the field is not a report filter, Excel raises an error rather than silently creating that item.

Select several report-filter items

For multiple selections, use the report filter’s multi-select setting and make the desired PivotItems visible. The code below selects East and West. It checks that both items exist before changing visibility, so a spelling mismatch does not leave the field with every item hidden. This approach is intended for ordinary, non-OLAP PivotTables.

Sub SetMultipleRegionFilters()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim hasEast As Boolean
    Dim hasWest As Boolean

    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("ptSales")
    Set pf = pt.PivotFields("Region")

    ' Confirm both requested items exist before hiding anything.
    For Each pi In pf.PivotItems
        If CStr(pi.Name) = "East" Then hasEast = True
        If CStr(pi.Name) = "West" Then hasWest = True
    Next pi

    If Not hasEast Or Not hasWest Then
        MsgBox "East or West was not found in the Region field."
        Exit Sub
    End If

    pf.ClearAllFilters
    pf.EnableMultiplePageItems = True

    ' Make all items visible first so the final selection is safe.
    For Each pi In pf.PivotItems
        pi.Visible = True
    Next pi

    ' Keep only the requested items.
    For Each pi In pf.PivotItems
        Select Case CStr(pi.Name)
            Case "East", "West"
                pi.Visible = True
            Case Else
                pi.Visible = False
        End Select
    Next pi
End Sub

Changing visibility item by item can be slower than setting one report-filter item, especially when a field has many categories. Do not remove the initial step that makes items visible: Excel can reject an operation that would hide the field’s last visible item. For Data Model or OLAP PivotTables, item visibility and page-field behavior can differ; test against a copy and use the object model appropriate to that source.

Filter a row or column field

If Region appears in the Rows or Columns area, a report-filter assignment does not apply. For a simple manual selection in a conventional PivotTable, the same PivotItems visibility concept can be used, but the field should remain in its existing layout area. Clear the field’s prior filter, confirm the requested item exists, make the items visible, and then hide the unwanted ones. As with multi-select report filters, never hide every item.

For more specific conditions—such as showing only labels that begin with “North,” dates in a period, or values above a threshold—use a label, date, or value filter rather than manually hiding named items. These filters encode a rule, so they can include newly appearing items that meet the condition. The exact VBA method and arguments depend on the filter type and Excel object model; do not substitute a CurrentPage assignment for a rule-based filter.

Refresh timing and changing source data

A PivotTable can only filter items present in its PivotCache. If an item was added to the source but does not appear in the dropdown, refresh the PivotTable and verify that its source range includes the new rows. A refresh does not automatically repair an incorrectly sized fixed source range. Consider using an Excel Table as the source so new rows are more readily included when the PivotTable is refreshed.

When several PivotTables share a cache, refreshing one may update the shared cache for the others, but their displayed layouts and filters can still need separate attention. For a macro that changes both data and filters, a sensible order is: update the source, refresh the relevant PivotTable or cache, then apply the selection. If the macro runs repeatedly, avoid refreshing unnecessarily because refresh can take time and may trigger connections or calculations.

Common errors and practical checks

“Subscript out of range”: Check the worksheet and PivotTable names, including spelling. A PivotTable name is not necessarily its tab name.
“Unable to get the PivotFields property” or a missing field: Confirm the field caption and that the selected PivotTable uses the expected source.
CurrentPage fails: Confirm the field is in the report-filter area, multiple selection is disabled, and the item text exists after refresh.
Excel refuses to hide an item: The operation may hide the last visible item, or the field may be controlled by an OLAP/Data Model source. Check the field type and selection sequence.
Filter appears unchanged: Check whether another field, slicer, timeline, or report connection is also filtering the PivotTable. A second control can make the visible result seem unexpected.
New category is unavailable: Check the source range and refresh the cache; changing VBA filter code cannot add a missing source item.

During development, use the VBA editor’s Debug > Compile VBAProject command to catch syntax problems, and step through code with F8 when necessary. A clear error message is more useful than suppressing errors with On Error Resume Next, which can hide misspelled names and leave the PivotTable in an unintended state.

When to use VBA, and when not to

VBA is useful when a filter must change from a button, a scheduled workflow, or values entered elsewhere in the workbook. For a one-time selection, the PivotTable dropdown is simpler and does not require enabling macros. A slicer can also provide a more visible interactive control for readers of a report.

If automation is necessary, keep the procedure focused: validate names and items, update the source only when needed, apply the filter, and show a helpful message if a requested item is unavailable. Avoid hard-coding a changing list of categories if a rule-based value or label filter better expresses the requirement.

Frequently Asked Questions

Can VBA select “(All)” in a PivotTable filter?
For a single-select report filter, use pf.ClearAllFilters to remove the field’s filter and show all items. This is generally clearer than assigning the displayed “(All)” caption, which is not a regular data item.

Do I have to refresh before changing a PivotTable filter?
No. Refresh only when the source data or available items have changed, or when the PivotTable needs to recalculate from its source. For a selection among existing items, the filter can usually be changed directly.

Why does VBA say the PivotField item does not exist?
The requested item may be spelled differently, may include spaces, or may not yet be in the refreshed cache. Check the field’s visible item captions and confirm the source range contains the record.

Can this macro run in Excel for the web?
No. Excel for the web can open workbooks containing VBA, but it cannot create, edit, or run VBA macros. Run the macro in the desktop Excel application.

Will changing a PivotTable filter update its source data?
No. A filter changes which records are included in the displayed summary; it does not edit the source table. To change source records, edit the underlying data and refresh the PivotTable as needed.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.