Excel VBA: How to Edit a PivotTable

VBA & macros · Updated November 21, 2026

Yes. In Excel for Microsoft 365 on Windows, VBA can edit a PivotTable by changing its fields, layout, filters, formatting, or source data, then refreshing it. The basic pattern is to get the PivotTable object from its worksheet and set the properties you want to change.

What “edit a PivotTable” can mean

A PivotTable is a summary view built from source data, so editing it with VBA usually means changing the summary rather than changing the underlying records. A macro can place fields in the Rows, Columns, Values, or Filters areas; change a value calculation; apply a filter; set a number format; refresh results; or point the PivotTable at a different source.

For example, a sales report might show revenue by month today and revenue by region tomorrow. VBA can move the Region field into the Rows area and make Sales a summed value field. The source records remain unchanged. If a source transaction is wrong, correct the source data and refresh the PivotTable instead of trying to edit the summarized cell.

Excel exposes PivotTables through its object model. The usual route is Workbook, then Worksheet, then PivotTable, then PivotField. A PivotField represents a source column such as Region or Sales; its Orientation determines where it appears in the report.

Example: change the fields and refresh

This example assumes a worksheet named Summary, a PivotTable named SalesPivot, and source columns whose exact headings are Region and Sales. It puts Region in Rows and Sales in Values, summarizes Sales by sum, applies a number format, and refreshes the table. The Sales source column needs numeric values for a sum.

Before running a macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, so a backup is the simplest way to recover the previous layout if the result is not what you intended.

Sub EditSalesPivotTable()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim oldScreenUpdating As Boolean
    Dim errorText As String

    oldScreenUpdating = Application.ScreenUpdating
    On Error GoTo Failed
    Application.ScreenUpdating = False

    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("SalesPivot")

    ' Defer repeated layout updates until the changes are made.
    pt.ManualUpdate = True

    With pt.PivotFields("Region")
        .Orientation = xlRowField
        .Position = 1
    End With

    With pt.PivotFields("Sales")
        .Orientation = xlDataField
        .Function = xlSum
        .NumberFormat = "$#,##0.00"
    End With

    pt.ManualUpdate = False
    pt.RefreshTable

CleanExit:
    Application.ScreenUpdating = oldScreenUpdating
    Exit Sub

Failed:
    errorText = Err.Description
    On Error Resume Next
    If Not pt Is Nothing Then pt.ManualUpdate = False
    Application.ScreenUpdating = oldScreenUpdating
    On Error GoTo 0
    MsgBox "The PivotTable was not fully updated: " & errorText, vbExclamation
End Sub

To use it, press Alt+F11 on Windows to open the Visual Basic Editor, choose Insert > Module, paste the code, and change the worksheet name, PivotTable name, and field names to match your file. Run it from the editor or assign it to a button. On Mac, Option+F11 commonly opens the Visual Basic Editor; depending on keyboard settings, you may also need the Fn key. Excel for the web can display a workbook containing macros but cannot create or run VBA macros.

Find the PivotTable and field names first

Names are a frequent source of errors. The worksheet tab name is not necessarily the PivotTable name, and field names must match the source column headings. Click inside the PivotTable and use PivotTable Analyze on the ribbon to find its name in the PivotTable Name box. Check the source header row for the exact field spelling, including spaces.

If you are unsure which PivotTables are on a worksheet, inspect the PivotTables collection in the Visual Basic Editor’s Immediate window or write a short inspection macro. A workbook may contain several PivotTables on one sheet, so referring to the one by name is generally safer than assuming it is the first one.

Also check whether a field is already used in the report. Setting a field’s Orientation moves it to a different area; setting it to xlHidden removes it from the layout. If you intend to show the same source field more than once—for example, two copies of a value with different calculations—use the PivotTable’s data-field methods and assign distinct captions rather than assuming one PivotField setting will create both appearances.

Change summaries, filters, and layout

After a field is in the Values area, its Function property controls the calculation. Common constants include xlSum, xlCount, xlAverage, xlMax, and xlMin. The appropriate calculation depends on the data: summing a numeric Sales column is different from counting rows or averaging a per-transaction value. For nonnumeric data, Count is often the useful summary.

Row and column placement is controlled by Orientation and Position. The constants xlRowField and xlColumnField place fields on those axes; xlPageField places one in the report filter area; xlDataField puts a field in Values. To remove a field from the visible layout, set its Orientation to xlHidden. After rearranging multiple fields, assign positions deliberately so the order is predictable.

Filters can also be changed programmatically, but the exact approach depends on the filter type and PivotTable source. A report filter, a label filter, a value filter, and a slicer are different controls. For a macro that must select particular items, first confirm that those items exist and decide what should happen if the source data no longer contains one of them. A hard-coded item name can make a macro fail or produce an unexpected report when the data changes.

Refresh data and change the source

Refresh recalculates a PivotTable from its existing source; it does not automatically repair an incorrect source range or change the source itself. You can refresh a selected PivotTable with RefreshTable in VBA, use PivotTable Analyze > Refresh for the selected table, or use Data > Refresh All to update workbook connections and PivotTables. Refresh All may take longer if the workbook has external connections.

A source range that grows over time is easier to maintain when it is an Excel Table. Tables expand as records are added, and a PivotTable based on the table can include those records after refresh. With a fixed cell range, new rows may sit outside the source and therefore not appear in the report until the source is updated.

Changing the source is a different operation from refreshing. In the interface, select the PivotTable and choose PivotTable Analyze > Change Data Source. VBA can change a PivotTable’s PivotCache, but this requires creating or assigning a cache with a valid source. Check the workbook’s existing PivotTables and cache arrangement before changing it: several tables may share a cache, and a cache change can affect more than the one report you intended to edit.

Classic PivotTables, Data Model, and limitations

Not every PivotTable supports the same operations. A conventional PivotTable built from a worksheet range typically supports features such as calculated fields through its PivotFields and CalculatedFields collections. A PivotTable based on the Excel Data Model or an OLAP source has different rules; calculations may use measures, and some classic field operations or properties are unavailable or behave differently.

If code that works on a range-based PivotTable fails on a Data Model PivotTable, do not assume the field name is wrong. Check the source type and the relevant object-model documentation. Data Model reports may require working with measures, hierarchies, or cube fields rather than treating every item as an ordinary worksheet column. Slicers and timelines also have their own objects and connections; changing a PivotField alone does not necessarily reproduce every user-interface action.

There are practical limits, too. PivotTables may be protected, the source may be unavailable, or a field may contain mixed data types. A macro should account for these possibilities and give a useful error rather than silently leaving a partly changed report. If the layout is being edited repeatedly, setting ManualUpdate while making several changes can reduce intermediate recalculations, but turn it off afterward so Excel can update the report.

Common problems and how to avoid them

  • “Subscript out of range”: Usually the named worksheet or PivotTable does not exist in the workbook referenced by the code. Confirm names and whether the code should use ThisWorkbook (the workbook containing the macro) or another workbook.
  • Field not found: The source heading may differ from the string in the code, or the PivotTable may use a Data Model or external source with different field identifiers. Check the source and field list.
  • Sum is unavailable or gives the wrong result: The source may contain text, blanks, or mixed types. Inspect and clean the source, and choose Count or another appropriate summary when summing is not meaningful.
  • New rows do not appear: Refreshing cannot include records outside a fixed source range. Expand or reset the source, or use an Excel Table as the source for a growing list.
  • The macro appears to do nothing: Check that macros are enabled, the workbook is saved in a macro-enabled format such as .xlsm, and the code references the intended workbook and PivotTable.
  • Undo does not restore the old layout: VBA actions clear or bypass Excel’s normal undo history. Close without saving or restore the backup if you need to return to the earlier state.

Windows, Mac, and macro safety

The sample uses standard Excel VBA PivotTable objects and is intended for Microsoft 365 on Windows. Excel for Mac supports VBA, but editor access, keyboard shortcuts, available features, and some external-connection behavior can differ. Avoid assuming that Windows-specific components such as ActiveX controls are portable to Mac. Excel for the web does not execute VBA; a workbook must be opened in desktop Excel to run the macro.

Macros can change workbook content, so enable them only in files you trust. If Excel blocks a downloaded file, follow your organization’s security policy rather than bypassing protections indiscriminately. Store reusable code in a trusted location or a controlled personal macro workbook only when that is appropriate for your environment. Keep a clean backup before automating a report that matters.

When a manual edit or another tool is better

For a one-time adjustment, using the PivotTable Fields pane is often faster and easier to verify than writing code. Drag a field between Rows, Columns, Values, and Filters, then use the field’s menu to change its calculation or number format. VBA becomes useful when the same change must be repeated, when reports must be prepared consistently, or when a button should refresh and arrange several PivotTables in sequence.

For repeatable data cleanup and reshaping before analysis, Power Query may be a better fit than a macro that edits source cells. For automatic summaries that need to update as formulas recalculate, worksheet formulas or dynamic array functions may be appropriate, depending on the Excel version. These approaches do not replace every PivotTable feature; choose based on whether the task is transforming source data, summarizing it interactively, or automating a repeatable report layout.

Frequently Asked Questions

Can VBA change a PivotTable’s source data?
It can change the source used by a PivotTable by updating its cache or source configuration, but that is separate from refreshing. Verify the source range, connection, and any other PivotTables that share the cache before making the change.

How do I refresh a PivotTable with VBA?
Get the PivotTable object and call its RefreshTable method. For example, a macro can use the worksheet’s PivotTables collection to identify a named table and then refresh it. Use Refresh All from the ribbon when the goal is to update multiple workbook connections and reports.

Can VBA edit a PivotTable in Excel for the web?
No. Excel for the web can open and display a macro-enabled workbook, but it does not run VBA. Open the file in the desktop version of Excel to execute VBA code.

Why does my macro work on one PivotTable but not another?
The tables may use different sources, field names, caches, or features. A Data Model or OLAP PivotTable, for instance, does not support every operation available to a conventional range-based PivotTable. Check the source type and the exact field names.

Can I undo a PivotTable edit made by VBA?
Usually not with Ctrl+Z. Macro actions do not provide the normal worksheet undo history, so save a copy before running the code. If the macro has already run, restore the backup or manually recreate the previous layout.

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.