VBA Modify Pivot Tables in Excel: Examples and Tips

VBA & macros · Updated November 18, 2026

Yes. VBA can modify an Excel PivotTable by moving fields between rows, columns, filters, and values; setting report filters; changing some layout options; and refreshing the table. In desktop Excel for Microsoft 365 on Windows, use the PivotTable object model, and save a copy of your workbook before running a macro because VBA changes cannot be undone with Ctrl+Z.

What VBA Can Change in a PivotTable

A PivotTable is an interactive summary built from source data. VBA can automate many of the same structural changes you make by dragging fields in the PivotTable Fields pane. For example, a macro can put Region in the Filters area, Product in Rows, and Month in Columns, then display a chosen region. It can also add a numeric field to Values and choose a summary such as sum or count.

VBA is useful when a report must be rebuilt consistently, when several PivotTables need the same adjustment, or when a workbook is prepared for a recurring process. It is not a substitute for fixing incomplete or inconsistent source data. A PivotTable cannot reliably summarize fields that are missing, mislabeled, or stored in the wrong data type; correct the source first.

  • Fields and layout: Set a field’s Orientation to xlRowField, xlColumnField, xlPageField, xlDataField, or xlHidden.
  • Filters: Set a report-filter field to a specific item with CurrentPage, provided that field is in the Filters area and the item exists.
  • Values: Add a field and specify a summary function, such as xlSum or xlCount.
  • Refresh: Refresh a PivotTable so it reflects available source data.

How the PivotTable Object Model Works

Excel VBA represents a PivotTable with a PivotTable object. You can get it from the worksheet that contains it, using the worksheet’s PivotTables collection. Each PivotTable has a PivotFields collection, and each field has properties such as Orientation and Position. A field’s Orientation determines which area it occupies; Position determines its order within that area.

Names matter. The worksheet name, PivotTable name, and field names in code must match the workbook. Field names generally correspond to the source column headers, not necessarily the captions currently visible in the report. A PivotTable can be named or inspected from the PivotTable Analyze tab, and the Field List shows the fields available from its source. If a field was renamed in the report, verify which name VBA recognizes before relying on it.

A PivotTable also uses a PivotCache, which stores a connection to and cached representation of the source. Several PivotTables may share a cache. That distinction matters: changing a field layout affects the selected PivotTable, while refreshing or changing a shared cache may affect other reports too. Avoid changing a cache or source range without checking whether other PivotTables depend on it.

Prepare the Workbook Before Running a Macro

Start with a backup copy. Macros are not reversible through Ctrl+Z, and even a small change can alter a report’s layout or filter state. Confirm the workbook has the intended source data, that the PivotTable exists, and that the field names in the code match the source headers. If the workbook uses external connections, permissions and connection availability can also affect refresh behavior.

  1. Save a working copy of the workbook, preferably as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain VBA.
  2. Check the worksheet, PivotTable, and source-field names; replace the example names in the code with the names in your file.
  3. Open the Visual Basic Editor with Alt+F11 on Windows, choose Insert > Module, and paste the macro into the module.
  4. Run the procedure from the editor or from Excel’s Macro dialog, then inspect the result before distributing the workbook.

On Mac, VBA is available in desktop Excel, but keyboard shortcuts can depend on the keyboard and macOS settings; use Developer > Visual Basic if the shortcut does not open the editor. Excel for the web can display workbooks containing VBA but cannot create, edit, or run VBA macros. Do not enable macros from an untrusted workbook merely to test code.

Example: Rearrange Fields and Set a Report Filter

This example assumes a worksheet named Report, a PivotTable named SalesPivot, and fields named Region, Product, and Month. It moves Region to the Filters area, puts Product in Rows and Month in Columns, then selects West. The West item must exist in the Region field. The macro does not create a PivotTable or add source fields that are absent.

Sub SetSalesPivotLayout()
    Dim ws As Worksheet
    Dim pt As PivotTable

    ' Use the worksheet and PivotTable names in your workbook.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set pt = ws.PivotTables("SalesPivot")

    ' Arrange the fields in the desired areas.
    pt.PivotFields("Region").Orientation = xlPageField
    pt.PivotFields("Region").Position = 1
    pt.PivotFields("Product").Orientation = xlRowField
    pt.PivotFields("Product").Position = 1
    pt.PivotFields("Month").Orientation = xlColumnField
    pt.PivotFields("Month").Position = 1

    ' Select a report-filter item that exists in the source.
    pt.PivotFields("Region").CurrentPage = "West"
End Sub

Changing the Orientation moves a field out of its previous area, so a field does not need to be manually removed first. To hide a field, set its Orientation to xlHidden. If the workbook already contains a filter selection that should be preserved, omit the CurrentPage line. For example, selecting West intentionally replaces the current Region report-filter selection.

Add a Value Field and Choose Its Summary

To add a numeric source field to the Values area, use the PivotTable’s AddDataField method. This separate example assumes that SalesPivot is on Report and that Amount is a numeric source field. It adds a sum with the displayed caption Total Amount:

Sub AddTotalAmount()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim amountField As PivotField

    ' Identify the existing report and numeric source field.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set pt = ws.PivotTables("SalesPivot")
    Set amountField = pt.PivotFields("Amount")

    ' Add Amount to Values and summarize it as a sum.
    pt.AddDataField amountField, "Total Amount", xlSum
End Sub

Run this as a one-time layout change: running it repeatedly can add the same measure again under another caption or create duplicate value fields. For a count, use xlCount instead; the appropriate summary depends on the data and the question the report should answer. If Excel displays Count rather than Sum when you add a field manually, check whether the source values are truly numeric and whether blanks or text entries are present.

Refresh Data and Understand Source Changes

To refresh one PivotTable in VBA, call its RefreshTable method:

Sub RefreshSalesPivot()
    Dim ws As Worksheet
    Dim pt As PivotTable

    ' Refresh the named PivotTable on its worksheet.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set pt = ws.PivotTables("SalesPivot")
    pt.RefreshTable
End Sub

The equivalent manual command is PivotTable Analyze > Refresh in Excel for Windows. Refreshing updates the report from its available source or cache; it does not necessarily fix a source range that excludes newly added rows. A practical design is to store source records in an Excel Table and use that table as the PivotTable source. Tables expand as records are added, after which the PivotTable still needs to be refreshed.

Changing the source itself is different from refreshing. A PivotTable based on a fixed cell range may need its source updated before new rows are included. VBA can change a PivotTable’s cache in certain situations, but this is more advanced: shared caches and OLAP or other external-model PivotTables have limitations, and changing a shared cache can affect multiple reports. Verify the source type and cache relationships before automating that operation.

Common Errors and How to Diagnose Them

The most frequent failures come from mismatched names or assumptions about the field. A “Subscript out of range” error often means the worksheet or PivotTable name is wrong. An error when accessing PivotFields can indicate that the field name is misspelled, absent from the source, or not available in that PivotTable’s data model. Check names in Excel rather than guessing from a chart title or a renamed report caption.

  • CurrentPage fails: Confirm that the field is in the Filters area, that the requested item is an exact available item, and that it is not a multi-select filter. A label such as West Coast is not interchangeable with West.
  • Sum is unavailable or gives unexpected results: Inspect the source column for text-formatted numbers, blanks, errors, or mixed types. Correct the source and refresh.
  • New rows do not appear: Confirm that the PivotTable source includes the rows. A refresh cannot include records outside a fixed source range.
  • A field cannot be moved: Consider whether the report is based on an OLAP source or the Data Model, which can expose different behavior from a standard range-based PivotTable.

For troubleshooting, test the macro on a copy and run one change at a time. If a procedure stops on a line, inspect the object and field names on that line, and verify the PivotTable’s source type. Avoid suppressing every error with broad error-resume code: it can make a macro appear successful even when the report was not changed.

VBA or Manual Editing: Which Is Better?

Manual editing is usually preferable for a one-off report or for exploring a new layout. The PivotTable Fields pane makes it easy to see available fields and understand the result as you move them. Use VBA when the same precise change must happen repeatedly, when a workbook has multiple reports to update, or when layout changes are one step in a documented reporting workflow.

VBA is not always the simplest automation choice. A PivotTable’s built-in refresh command, a workbook connection, or a data transformation in Power Query may better solve a recurring data-import problem. Power Query is designed to connect to, clean, and reshape data; a PivotTable summarizes it. These tools can be used together, but a macro that rearranges report fields does not replace data-cleaning steps upstream.

Also consider who will use the workbook. VBA requires a desktop version of Excel that can run macros, and an organization may restrict macro execution. If recipients use Excel for the web or need a report that works without macro permissions, save the final PivotTable layout in the workbook or choose a non-VBA workflow.

Frequently Asked Questions

Can VBA change a PivotTable filter?
Yes. For a standard report filter, place the field in the Filters area and set its CurrentPage to an item that exists. The method and available options can differ for multi-select filters, slicers, OLAP sources, and Data Model PivotTables.

Can VBA add or remove PivotTable fields?
Yes. Set a field’s Orientation to a row, column, filter, or data area to place it, or to xlHidden to remove it from the layout. The field must be available in the PivotTable’s source.

Does RefreshTable include rows added below the source range?
Not if the PivotTable’s source is a fixed range that excludes those rows. Expand or change the source, or use an Excel Table as the source and refresh the PivotTable afterward.

Can I undo a PivotTable macro with Ctrl+Z?
No. Excel clears the normal undo history when VBA runs, so Ctrl+Z cannot reverse the macro’s changes. Save a separate copy before running code and check its effects before saving over the original.

Can VBA PivotTable macros run in Excel for the web?
No. Excel for the web can open a macro-enabled workbook, but it cannot run or edit VBA. Use desktop Excel to run the macro, or choose a workflow supported by the web app.

Related

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.