VBA Pivot Table: Add a Filter with Code

To add a filter to an existing Excel PivotTable with VBA, set the relevant PivotField’s Orientation to xlPageField, then optionally set its CurrentPage to a specific item. For example, make the Region field a report filter and select West; the code below shows the exact steps.

What a PivotTable filter means

A PivotTable filter limits which source records contribute to the summary; it does not delete or alter those records in the source data. Excel offers several kinds of PivotTable filtering, and the best VBA method depends on where the field appears and what you want to keep.

Report filter: A field appears in a dropdown above the PivotTable. This is what xlPageField creates.
Label filter: Keeps row or column items whose captions meet a rule, such as product names beginning with A.
Value filter: Keeps row or column items based on summarized numbers, such as regions with sales greater than 10,000.
Slicer or timeline: Provides a visible button-based control, rather than a standard field dropdown.

These choices are not interchangeable. A report filter is convenient for selecting a category such as one region. A value filter answers a different question: which categories have a summary value above a threshold? Choosing the right filter type prevents a common mistake—adding a field to the report-filter area when the goal is actually to screen the summarized results.

Before you write VBA

The example assumes that the workbook already contains a conventional PivotTable on a worksheet named Summary. The PivotTable is named SalesPivot, and its source includes a column headed Region with an item named West. Replace those names with the actual worksheet, PivotTable, field and item names in your file.

In Excel for Microsoft 365 on Windows, select a cell in the PivotTable and find its name on the PivotTable Analyze tab, in the PivotTable Name box. Field names are based on the source column headings, while item names are the values in that column. Check the headings and values carefully: a trailing space or a changed source caption can make a reference fail.

Open the Visual Basic Editor with Alt+F11 on Windows, choose Insert > Module, and paste the macro into the module. On Mac, use Option+F11 (on some keyboards, also hold Fn) or open Visual Basic from the Developer tab. Excel for the web can display a workbook containing macros, but it cannot run VBA; use the desktop app.

Add a report filter to an existing PivotTable

This macro moves Region into the report-filter area, places it first among report filters, selects West, and refreshes the PivotTable. It does not create a new PivotTable or change its source range.

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

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

    ' Put Region in the report-filter area and select one item.
    pf.Orientation = xlPageField
    pf.Position = 1
    pf.CurrentPage = "West"

    ' Update the displayed PivotTable.
    pt.RefreshTable
End Sub

After the macro runs, Region appears in a filter control above the PivotTable, and the table summarizes rows for West. Setting Orientation to xlPageField is the key operation. If the field was previously in the row or column area, this changes its placement; it does not add a second copy of that same field.

If you only want to add the dropdown and leave it showing all available items, omit the pf.CurrentPage = "West" line. If the selected item is not present in the PivotTable’s cache, Excel can raise an error. Verify that the spelling matches an available Region item and that the PivotTable has been refreshed from its source.

Choose the right kind of filter

For a simple selection such as West, a report filter is usually the most direct option. To select the item interactively, leave out the CurrentPage assignment and let the user choose from the dropdown. To select a different item in a later macro run, assign its caption to CurrentPage, provided that item exists.

For text-based filtering of row or column labels, Excel’s PivotField.PivotFilters collection supports label filters. The following example keeps Product captions that begin with A. It assumes an ordinary, non-OLAP PivotTable with a Product field. The macro clears existing filters on Product before applying the new label rule, so it replaces that field’s prior filter rather than stacking on top of it.

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

    ' Set these names to match the existing PivotTable.
    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("SalesPivot")
    Set pf = pt.PivotFields("Product")

    ' Label filters apply to a row or column field.
    pf.Orientation = xlRowField
    pf.ClearAllFilters
    pf.PivotFilters.Add2 Type:=xlCaptionBeginsWith, Value1:="A"

    pt.RefreshTable
End Sub

For a value rule, use the PivotTable field dropdown and choose Value Filters, then select a condition such as Greater Than and the summary field to evaluate. A value filter depends on the summarized measure—for example, Sum of Sales—so simply moving Sales into the report-filter area will not produce the same result. The VBA object model allows more advanced filter automation, but the field type, selected measure and PivotTable source all matter.

Multiple selections, dates and slicers

A report filter can be set to one item with CurrentPage. For multiple choices, the PivotField has an EnableMultiplePageItems property. Set it to True for a report-filter field when users need to select more than one item in the dropdown. A multi-selection is not represented by assigning several captions to CurrentPage; that property selects one page item. Code that hides and shows individual PivotItems needs extra care to avoid attempting to hide every item.

Date filtering may use date labels, grouped date fields, or a timeline, depending on how the PivotTable was built. A filter on a grouped Year field is not necessarily the same as a filter on the original transaction-date field. Before automating a date selection, inspect the field names shown in the PivotTable and confirm whether Excel has created separate Year, Quarter or Month fields.

Slicers are a useful alternative when the filter should be visible and easy to operate. They can connect to compatible PivotTables that share a source or PivotCache. A slicer is not simply a report filter with a different appearance: its cache and connections need to be managed, particularly when a workbook has several PivotTables. For a single automated selection, a report filter is generally simpler; for an interactive dashboard, a slicer may be clearer.

Refresh behavior and changing source data

Changing a PivotField’s orientation or filter selection updates the PivotTable layout, but it does not necessarily import newly added source records. pt.RefreshTable refreshes that PivotTable from its existing cache. If new rows were added beyond a fixed source range, first ensure that the source range includes them. Using an Excel Table as the source is often helpful because its range expands as rows are added, though the PivotTable still needs a refresh to incorporate the new records.

A refresh can also affect which items are available. If West was removed from the source and the cache no longer contains it, the assignment to CurrentPage may fail. Conversely, old items can sometimes remain available in a PivotTable cache depending on its settings. When a macro must always select a valid item, check that the requested caption is present rather than assuming that a familiar category still exists.

When automating several layout changes, developers sometimes set pt.ManualUpdate = True to postpone repeated updates, then set it back to False after the edits. That can improve efficiency for larger PivotTables, but the setting should be restored even if an error occurs. For a small change like the example above, leaving ManualUpdate alone makes the macro easier to understand.

Common errors and how to resolve them

Subscript out of range: The worksheet or PivotTable name does not match the workbook. Confirm the tab name and the PivotTable Name box; the cell address is not the PivotTable’s name.
Unable to get the PivotFields property: The field caption may be wrong, the field may not be part of the PivotTable source, or the PivotTable may use a data model structure that exposes fields differently.
CurrentPage assignment fails: The field may not be in the report-filter area, the item may not exist in the cache, or the PivotTable may use OLAP or Data Model fields with different member identifiers. Confirm the field type and available item names.
PivotFilters.Add2 fails: Confirm that the field is a row or column field and that the PivotTable supports the requested filter. Data Model and OLAP PivotTables can behave differently from ordinary range-based PivotTables.
Macro does not run: The workbook may be open in Excel for the web, macros may be disabled by security settings, or the file may not be saved in a macro-enabled format.

For troubleshooting, run the macro one line at a time in the Visual Basic Editor with F8 on Windows, and inspect the highlighted line when an error occurs. On Mac, the Visual Basic Editor offers step-through debugging as well, though the keyboard shortcut can depend on the keyboard configuration. Avoid changing the source data and PivotTable layout at the same time until the basic field reference works.

Saving and running the macro safely

Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) if it needs to retain VBA code. Before running a macro, save a separate copy of the workbook. VBA actions cannot be reversed with Ctrl+Z, and a macro may change more than the visible filter if it contains additional instructions.

Only enable macros in files from sources you trust. Excel may block macros or show a security notice based on the file’s origin and your organization’s policies; do not lower security protections just to run an unfamiliar workbook. For a controlled workbook, use the organization’s approved process and test the macro on a copy with representative data first.

Frequently Asked Questions

How do I add a filter to a PivotTable in VBA?
Get the target PivotField and set its Orientation to xlPageField. To select one item in the resulting report filter, assign that item’s caption to CurrentPage.

Does xlPageField filter the source data?
No. It places a field in the PivotTable’s report-filter area and changes which records are included in the summary. It does not remove rows from the original source range.

How do I clear a PivotTable field’s filter in VBA?
Call ClearAllFilters on the relevant PivotField. This clears filters applied to that field, so use it carefully if the field has a selection or label rule you want to retain.

Can VBA select more than one report-filter item?
Yes. Enable multiple page items on the report-filter field with EnableMultiplePageItems = True, then manage the individual PivotItems. CurrentPage itself is for selecting one page item, not a list of captions.

Can Excel for the web run this VBA?
No. Excel for the web can open and edit many workbook contents, but VBA macros must be run in the Excel desktop application. Use Excel for Windows or Mac and follow the workbook’s macro-security settings.

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.