VBA: Filter a PivotTable by Cell Value Automatically

To filter an Excel PivotTable from a cell, use VBA to read that cell and assign its value to the PivotField’s CurrentPage property. The field must be in the PivotTable’s Filters area, and the cell’s value must match one of its available items; the example below also shows how to run the macro when the cell changes.

How cell-based PivotTable filtering works

A PivotTable does not automatically use an arbitrary worksheet cell as a filter. VBA acts as the link: it reads a control cell, finds a named PivotTable and field, and applies the matching item. For example, a cell containing “West” can set a Region report filter to West. The user can then change the cell to “East” and run the macro again.

This method uses a report filter, also called a page field. It selects one item from a PivotField, such as a region, product category, salesperson, or year. It is different from a Value Filter, which keeps or excludes PivotTable items according to calculated values such as sales greater than a threshold. A cell-driven report filter is usually the simplest approach when the cell contains an item label.

Prepare the worksheet and PivotTable

The sample macro assumes the workbook has a worksheet named Control with the desired filter value in B2. A second worksheet, Summary, contains a PivotTable named SalesPivot. That PivotTable has a field named Region, and Region is already in the Filters area. Change these names in the code to match your workbook exactly.

Enter an item that exists in the PivotTable field in Control!B2, such as West.
Confirm the PivotTable name: click inside it and look for the name on the PivotTable Analyze tab.
Check the field caption in the PivotTable. The VBA name must match the field name, not necessarily a renamed worksheet label.
Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z.

Field and item names can contain spaces. Use the exact worksheet tab names and PivotTable field caption in the quoted strings. If the field is currently in Rows or Columns, move it to Filters first, or adapt the method to use a different kind of filter.

VBA macro for a report-filter field

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, then paste this procedure into the standard module. Save the file as an Excel Macro-Enabled Workbook (.xlsm) so the VBA code is retained.

Option Explicit

Public Sub FilterPivotFromCell()
    Dim wsControl As Worksheet
    Dim wsPivot As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim item As PivotItem
    Dim criterion As String
    Dim found As Boolean
    Dim oldScreenUpdating As Boolean
    Dim failMessage As String

    oldScreenUpdating = Application.ScreenUpdating
    On Error GoTo CleanFail

    Set wsControl = ThisWorkbook.Worksheets("Control")
    Set wsPivot = ThisWorkbook.Worksheets("Summary")
    Set pt = wsPivot.PivotTables("SalesPivot")
    Set pf = pt.PivotFields("Region")
    criterion = Trim$(CStr(wsControl.Range("B2").Value2))

    If pf.Orientation <> xlPageField Then
        Err.Raise vbObjectError + 1000, "FilterPivotFromCell", _
                  "Region must be in the PivotTable Filters area."
    End If

    ' A blank cell or (All) clears this field's filter.
    If Len(criterion) > 0 And LCase$(criterion) <> "(all)" Then
        For Each item In pf.PivotItems
            If StrComp(item.Name, criterion, vbTextCompare) = 0 Then
                found = True
                Exit For
            End If
        Next item

        If Not found Then
            Err.Raise vbObjectError + 1001, "FilterPivotFromCell", _
                      "The cell value is not an item in the Region field."
        End If
    End If

    Application.ScreenUpdating = False
    pf.EnableMultiplePageItems = False
    pf.ClearAllFilters

    If Len(criterion) > 0 And LCase$(criterion) <> "(all)" Then
        pf.CurrentPage = criterion
    End If

    Application.ScreenUpdating = oldScreenUpdating
    Exit Sub

CleanFail:
    failMessage = Err.Description
    Application.ScreenUpdating = oldScreenUpdating
    MsgBox "The PivotTable filter was not applied: " & failMessage, vbExclamation
End Sub

The code checks that Region is a report-filter field and that the cell value is an available item before changing the filter. A blank cell, or the text “(All),” clears the Region filter. The check avoids silently accepting a misspelled or unavailable item. The comparison ignores letter case, but it still requires the item text to match; trim leading and trailing spaces in the input.

Run the macro when the cell changes

To apply the filter immediately after a user edits B2, add a worksheet change event. In the Visual Basic Editor, find the Control sheet under Microsoft Excel Objects and paste the event procedure into that sheet’s code window—not into the standard module. The event calls the public macro stored in the module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("B2")) Is Nothing Then Exit Sub

    On Error GoTo Finish
    Application.EnableEvents = False
    FilterPivotFromCell

Finish:
    Application.EnableEvents = True
End Sub

Disabling events while the macro runs prevents a chain of worksheet events if later code changes cells. The handler restores events at the end. If you use a different input cell, update the range in the event procedure as well as the cell reference in the main macro.

Worksheet_Change runs when a person or another macro changes a cell’s contents. It does not run just because a formula recalculates and returns a new result. If B2 contains a formula, use a Worksheet_Calculate event with suitable safeguards, or call the filter macro from the procedure that updates the formula’s inputs. Avoid repeatedly applying the filter on every calculation unless that behavior is needed.

Run it manually or use a button

You do not need an event to use the macro. Select Developer > Macros, choose FilterPivotFromCell, and select Run. If the Developer tab is hidden, enable it in Excel’s ribbon settings. You can also add a Form Control button from Developer > Insert and assign the macro, giving users a clear “Apply filter” action without making the update automatic.

Excel for Mac supports VBA macros, although the Visual Basic Editor shortcut can vary by keyboard: try Option+F11 or Fn+Option+F11, or open the editor from the Developer tab. Excel for the web cannot run or edit VBA macros; use the desktop Excel application. Macro security settings may also require you to allow macros for a trusted workbook.

When the filter is not a single item

CurrentPage is intended for a single selected item in a report-filter field. The example deliberately sets EnableMultiplePageItems to False. If users need to select several items from the cell, define a clear input format—such as a comma-separated list—and write code that validates each item and sets the field’s visible items accordingly. That is a different task: setting one CurrentPage value will not apply a list of selections.

For a numeric condition such as “show regions with sales above the amount in B2,” do not treat the amount as an item caption. That request is a Value Filter based on a data field or measure. Excel’s PivotTable Value Filters and the PivotField PivotFilters collection are the relevant tools; the filter must refer to the correct data field, and the PivotTable needs to be refreshed if its source data has changed. Decide whether the cell represents an item to select or a threshold to compare before choosing VBA code.

Common errors and ways to troubleshoot

“Subscript out of range”: Check the spelling of Control and Summary and make sure both worksheet tabs exist in the workbook containing the macro. This code uses ThisWorkbook, meaning the workbook where the VBA code is stored.
Field or PivotTable not found: Check SalesPivot in the PivotTable name box and Region in the PivotTable field list. Captions changed in the layout may not be the underlying field name.
Item does not match: Compare the cell’s contents with the field’s item labels. A date, number, grouped item, or value displayed with formatting may not have the same underlying text as the cell.
The field is not a report filter: Move it into the Filters area or use a method appropriate to a row or column field. CurrentPage is not a general-purpose filter setting for every PivotField orientation.
A new source item is missing: Refresh the PivotTable so its cache includes the item, then run the macro again. A filter cannot select an item the PivotTable does not currently know about.
The code behaves differently with a Data Model PivotTable: OLAP and Data Model PivotTables can expose different restrictions from a conventional PivotTable. The sample uses the ordinary PivotItems and CurrentPage pattern; test a copy and check the specific field behavior before adapting it.

For an easier diagnosis, first run the macro manually with a simple, known item such as West. Confirm the report filter changes as expected, then add the worksheet event or button. Separating the filter logic from its trigger makes it easier to tell whether a problem comes from the cell value, the PivotTable setup, or the event code.

Frequently Asked Questions

Can a PivotTable filter automatically when a cell changes?
Yes. Put a Worksheet_Change event in the worksheet module for the input cell and call a macro that applies the filter. This event responds to direct edits, not to formula results changing during recalculation.

Does the cell value have to match the PivotTable item exactly?
It must identify an available item. The sample ignores case and trims outside spaces, but dates, numbers, grouped labels, and displayed formats can still require deliberate conversion or matching logic.

Can I use a cell to clear the filter?
Yes. In the example, a blank cell or the text “(All)” clears the filter on Region. You can change that convention, but make sure it is documented for workbook users.

Can this macro filter a PivotTable row field?
Not through the sample’s CurrentPage assignment: it expects Region in the Filters area. Row and column fields need a different approach, such as controlling item visibility or applying an appropriate label or value filter.

Will this work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel to run the macro; Excel for Mac also supports VBA, with different editor shortcuts and some feature differences.

Choosing a practical setup

Use a report-filter field and CurrentPage when one cell should choose one named category. Add a worksheet event for immediate response, or use a button when users should decide when to apply changes. Use a Value Filter when the cell contains a threshold rather than a category. Keeping those cases distinct—and validating the input before changing the PivotTable—makes the workbook easier to maintain and reduces confusing filter errors.

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.