Excel VBA: Set a PivotTable Filter Value

To set a PivotTable report filter with VBA, get the PivotField and assign its item to CurrentPage: for example, pf.CurrentPage = "West". The field must be in the PivotTable’s Filters area, and “West” must match an available item; the example below shows the complete macro and explains alternatives for row and column filters.

What “set a PivotTable filter value” means

A PivotTable field can be filtered in more than one way, so the right VBA statement depends on what you mean by “filter value.” A report filter, also called a page filter, appears above the PivotTable. It selects one item from a field, such as West from Region. For this common case, Excel exposes the selected item through the field’s CurrentPage property.

A field in the Rows or Columns area behaves differently. You can show or hide particular items, or apply a label or value filter. For example, showing only the West row item is not the same operation as filtering a Sales field to values greater than 10,000. Identifying the field’s location and the kind of rule you want is the key first step.

The examples here are for desktop Excel for Microsoft 365 on Windows and a conventional PivotTable built from worksheet data. PivotTables based on the Data Model or an OLAP source can have additional restrictions, described below.

Set a report filter with CurrentPage

In this example, the worksheet is named Summary, the PivotTable is PivotTable1, and Region is already in the Filters area. The macro clears any existing filter on Region and selects West. Change the three names and the item caption to match your workbook.

Sub SetPivotPageFilter()
    Dim pt As PivotTable
    Dim pf As PivotField

    ' Get the PivotTable and its report-filter field.
    Set pt = ThisWorkbook.Worksheets("Summary").PivotTables("PivotTable1")
    Set pf = pt.PivotFields("Region")

    ' Remove the current Region filter, then select one item.
    pf.ClearAllFilters
    pf.CurrentPage = "West"
End Sub

Before running a macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z. The worksheet name, PivotTable name, and field name must be exact. You can find a PivotTable’s name by selecting it and using PivotTable Analyze > PivotTable Name. Field names come from the source data headers, not necessarily from a caption you have manually changed in the report.

ClearAllFilters removes filters from that field before the new selection is applied. It does not clear filters from every field in the PivotTable. If you want to preserve other filters on Region, omit that line; assigning a new CurrentPage value will select the requested item in the report filter.

Check the field and item before assigning

The report-filter field needs to be in the PivotTable’s Filters area. If Region is currently a row field, pf.CurrentPage = "West" will not turn it into a report filter. Move the field through the PivotTable Fields pane or set its orientation in VBA before using CurrentPage. For example, pf.Orientation = xlPageField places the field in the Filters area, but changing layout can affect a report, so do it only when that is intended.

The item must also exist in the PivotField’s available items. The caption may differ from what you expect because of spaces, punctuation, spelling, or source values stored as dates or numbers. A cell that looks like a date may be represented by a formatted date caption, and a numeric item may not match a text value that looks identical. Check the filter’s dropdown in Excel to see the actual available choice.

When the desired item comes from a worksheet cell, read the cell’s value into a variable and validate it before assigning it. This is useful for a control cell that lets a user choose a region. If the cell is blank or contains a value that is not an item, the assignment can fail. A macro should handle that possibility rather than silently assuming every input is valid.

Row and column filters are not report filters

For a field in Rows or Columns, filtering a named item usually means changing that item’s visibility, not setting CurrentPage. With a standard worksheet-based PivotTable, the following pattern leaves only West visible in the Region field. It first ensures West is visible, then hides the other items; this order helps avoid trying to hide every item.

Sub ShowOnlyWestRowItem()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem

    ' This example assumes Region is in Rows or Columns.
    Set pt = ThisWorkbook.Worksheets("Summary").PivotTables("PivotTable1")
    Set pf = pt.PivotFields("Region")

    ' Make the requested item visible before hiding the others.
    pf.PivotItems("West").Visible = True
    For Each pi In pf.PivotItems
        If pi.Name <> "West" Then pi.Visible = False
    Next pi
End Sub

This is a manual item selection, not a dynamic label or value rule. It can be fragile if item names change, if the PivotTable is rebuilt, or if the field is part of a Data Model or OLAP PivotTable. It can also be inappropriate when users should retain several visible items. For those cases, use the PivotTable’s built-in label or value filter, or design a suitable report-filter or slicer control.

Single selection, multiple selection, and filter rules

A report filter normally displays one selected item. If the field’s dropdown allows multiple selections, the interface and object model have additional behavior, and a simple CurrentPage assignment still represents a single selected page item. Do not assume that assigning one value creates a multi-item selection.

In a standard PivotTable, code can control individual item visibility, but it must leave at least one item visible. Hiding items one at a time without checking the target can cause an error or leave the report in an unintended state. OLAP and Data Model PivotTables handle member selections differently; properties that work for ordinary PivotItems may be unavailable or require unique member names rather than displayed captions.

Also distinguish choosing an item from applying a rule. Selecting West means “show this item.” A label filter such as “begins with W” or a value filter such as “Sales greater than 10,000” expresses a condition. A manual list of visible items does not automatically include newly added source items that meet a condition, while a label or value filter can be recalculated when the PivotTable refreshes.

Refresh timing and source data changes

If the source data has changed, refresh the PivotTable before applying a filter value that depends on the new data. Otherwise, the item may not yet exist in the PivotTable cache, even though it appears in the source range. You can refresh an individual table with pt.RefreshTable before setting CurrentPage. For multiple PivotTables sharing a cache, consider whether they should all be refreshed and whether a refresh will affect other reports.

Refresh behavior also depends on the source. A PivotTable based on a range may not include rows added below that range unless the source is an Excel Table or the source range is updated. A PivotTable based on an external connection may require a successful data refresh. A filter macro cannot select an item that the PivotTable has not loaded.

For repeatable reporting, consider this sequence: refresh the source if needed, refresh the PivotTable, confirm the target item is available, and then set the filter. If the macro runs automatically after a refresh, account for the possibility that refresh is asynchronous or fails; do not assume the new cache is ready merely because the refresh was requested.

Common errors and how to diagnose them

“Unable to set the CurrentPage property.” Confirm that the field is a report filter, that it permits the requested selection, and that the item exists in the current PivotTable cache.
Subscript out of range. Check the worksheet and PivotTable names. A workbook can have several PivotTables, and their names are not necessarily the sheet name or the visible title.
Field not found. Verify the source header and the field name used by the PivotTable. A renamed display caption can differ from the underlying PivotField name.
The item appears in the source but not the dropdown. Refresh the PivotTable and check its source range, filters, and cache. The source row may not be included in the PivotTable’s current source.
The code works on one file but not another. Compare the PivotTable source types. Data Model and OLAP tables may not support the same item-visibility operations as ordinary PivotTables.

For a simple troubleshooting check, inspect the PivotTable Fields pane and the filter dropdown in Excel before changing code. That helps separate a VBA naming problem from a layout, source-data, or refresh problem.

Run and maintain the macro safely

On Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, paste the code, and run the procedure from the editor or from Excel’s macro interface. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm). Macro settings may block code until the file is trusted or macros are enabled under your organization’s policy.

Excel for the web cannot run VBA macros. On Mac, use the desktop version of Excel and open the Visual Basic Editor from the Tools > Macro menu; keyboard behavior can differ by keyboard and function-key settings. VBA is available in desktop Excel for Mac, but workbook compatibility and individual object-model behavior should be checked when sharing a file between platforms.

For maintainable code, keep the worksheet, PivotTable, field, and requested item names together in a clearly documented section. If a report is reused with different choices, read the desired item from a validated cell rather than editing the macro each time. Test with a copy first, especially if the PivotTable shares its cache with other reports or the macro changes field visibility.

Frequently Asked Questions

How do I filter a PivotTable to one value in VBA?
Put the field in the Filters area, then assign the item to its CurrentPage property, such as pf.CurrentPage = "West". The item must exist in the PivotTable.

Why does CurrentPage give an error?
Most often, the field is not in the Filters area, the item is not available in the cache, or the PivotTable uses an OLAP or Data Model source with different member behavior. Check the layout and dropdown first.

How do I clear a PivotTable field filter in VBA?
Use pf.ClearAllFilters for the specific PivotField. This clears that field’s filter; it does not clear filters on the other fields in the PivotTable.

Can VBA select more than one report-filter item?
A simple CurrentPage assignment selects one item. Multi-selection requires different handling, and the appropriate approach depends on whether the PivotTable uses a standard worksheet cache or an OLAP/Data Model source.

Can I run the macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel to run VBA; a slicer or the PivotTable filter dropdown can be a practical manual alternative.

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.