VBA Code to Update a PivotTable in Excel Automatically

VBA & macros · Updated November 18, 2026

To update one PivotTable in Excel for Microsoft 365 on Windows, call its RefreshTable method from a VBA macro. For example, assign the worksheet and PivotTable by name, then run pt.RefreshTable; to refresh several PivotTables, loop through the workbook’s sheets and PivotTables. Refreshing updates the report from its existing source—it does not necessarily change the source range or redesign the PivotTable.

What updating a PivotTable means

People use “update” to mean a few different things. Usually they want a PivotTable to show recently added or edited source records. That task is a refresh. The PivotTable keeps its existing fields, layout, filters, and value calculations while its underlying data is refreshed.

Other requests require more than a refresh. If new rows fall outside a fixed source range, the PivotTable may refresh successfully but still omit those rows. If a user wants a different grouping, filter, calculation, or field arrangement, VBA must change those settings explicitly. Keeping these tasks separate helps diagnose why a macro ran without producing the expected report.

A PivotTable uses a PivotCache, a stored representation of its source data. A refresh tells Excel to update the cache and report, subject to the source type and connection settings. Multiple PivotTables can share a cache, so refreshing one may affect other reports based on that same source.

VBA code to refresh one PivotTable

In this example, the worksheet is named Summary and the PivotTable is named SalesPivot. Replace both names with the actual names in your workbook. The code calls RefreshTable on that PivotTable.

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

    ' Set these names to match the workbook.
    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("SalesPivot")

    ' Refresh the PivotTable from its current source.
    pt.RefreshTable
End Sub

ThisWorkbook means the workbook that contains the macro. That is often safer than ActiveWorkbook, which could refer to a different workbook if focus changes. The macro will stop with an error if the worksheet or PivotTable name is misspelled, so check those names before running it.

Refresh every PivotTable in the workbook

To refresh all PivotTables in the workbook containing the code, loop through each worksheet and its PivotTables collection. This is useful when a report workbook has several sheets and the macro should update all of them.

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

    ' Visit every worksheet and PivotTable in this workbook.
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws
End Sub

This loop targets PivotTables; it is not the same as refreshing every connection or query in the workbook. A workbook may contain Power Query queries, external data connections, or other refreshable objects that need their own refresh process. Also, if many PivotTables share a cache, looping over each table can do redundant work. For a small or ordinary report workbook, the simple loop is easy to maintain; for a large model, investigate its cache and connection design before optimizing.

Set up and run the macro

  1. Save a copy of the workbook before running any macro. VBA actions generally cannot be reversed with Ctrl+Z.
  2. Save the working file as an Excel Macro-Enabled Workbook (.xlsm) so it can retain VBA code.
  3. On Windows, open the Visual Basic Editor with Alt+F11, or use Developer > Visual Basic. On Mac, use Tools > Macro > Visual Basic Editor; keyboard shortcuts can vary with the keyboard and macOS settings.
  4. In the editor, choose Insert > Module, paste the macro into the new module, and change the sample worksheet and PivotTable names as needed.
  5. Return to Excel, select a cell in the workbook, and run the macro from Developer > Macros. Select the procedure and choose Run. On Windows, Alt+F8 opens the Macros dialog; Mac shortcut behavior varies, so the ribbon route is a reliable alternative.

If Developer is not visible on Windows, enable its tab through File > Options > Customize Ribbon and select Developer. Excel for the web can display a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Open the workbook in the desktop Excel app to use this code.

Make new source rows part of the PivotTable

A common misconception is that refresh automatically expands every source range. It does not. If the PivotTable source is a fixed address such as A1:F500 and new records are entered in row 501, those records may remain outside the source even after refresh. The macro can run correctly while the report still appears unchanged.

A practical way to reduce this problem is to convert the source data into an Excel Table before creating the PivotTable. Select a cell in the source and choose Insert > Table, confirm the range and headers, and give the table a clear name on the Table Design tab. Build the PivotTable from that table. New rows added to the table can then be included in the source when the PivotTable is refreshed. Confirm the PivotTable’s source if the report was originally created from a fixed range.

This setup is especially useful when a workbook is refreshed repeatedly as a data list grows. It does not remove every source-data requirement: the table still needs consistent column headers, and new records must actually be added to the table. If the report uses an external connection or the Data Model, its refresh behavior can depend on connection settings and the model rather than a simple worksheet range.

Refresh the PivotTable when the workbook opens

If the report should update whenever someone opens the workbook, an event macro can call the refresh procedure. Put this code in the ThisWorkbook object in the Visual Basic Editor, not in a standard module. It runs when the workbook opens, provided macros are enabled.

Private Sub Workbook_Open()
    ' Run the refresh procedure stored in a standard module.
    RefreshAllPivotTables
End Sub

Automatic refresh can be convenient for a local source table, but it can surprise users if refreshing takes time or depends on a network connection. For workbooks with external queries, a refresh may run asynchronously, meaning Excel has not finished retrieving data when the next line of VBA begins. In that case, use the connection’s supported refresh settings and completion behavior rather than assuming that an immediate PivotTable loop waits for every query. Test the opening workflow on a copy and consider whether users should see a clear status message or choose when to refresh.

Refresh a PivotTable or refresh all data?

Choose the narrowest operation that matches the task. Calling RefreshTable updates a particular PivotTable. A loop updates PivotTables throughout the workbook. Excel’s Refresh All command and the Workbook.RefreshAll VBA method target workbook data connections and refreshable reports more broadly, which may include queries and connections as well as PivotTables.

Broader refreshes can take longer and may involve credentials, privacy settings, network availability, or external data permissions. They are not automatically better. If a macro only needs to update one report from a local table, refreshing that PivotTable is easier to reason about. If the report depends on an upstream query that must first fetch current data, refreshing only the PivotTable may leave it based on stale query output; refresh the relevant source first and account for its completion.

Common problems and checks

  • “Subscript out of range” or an object lookup error: Check the exact worksheet tab name and the PivotTable name. These are separate names; a sheet called Summary does not imply that its PivotTable is also called Summary.
  • The macro runs but totals do not change: Verify that the source records changed, that the source range includes them, and that filters or slicers are not hiding the affected items.
  • New categories or items are absent: Check whether the source is a fixed range, whether the new rows belong to the Excel Table, and whether the PivotTable’s field filters exclude the items.
  • Excel asks for credentials or cannot reach the source: The VBA method cannot bypass connection permissions or make an unavailable server accessible. Resolve the source or access issue through the workbook’s normal connection settings.
  • Excel blocks the macro: Use a trusted, appropriately secured workbook and follow your organization’s macro policy. Do not enable macros in a file merely because it contains a refresh button.

For diagnostics, first try refreshing the PivotTable manually from its PivotTable Analyze tab. If manual refresh also fails, the issue is more likely to be the source, cache, connection, or layout than the VBA loop. If manual refresh works, compare the macro’s object names and the workbook it targets.

Frequently Asked Questions

What is the VBA command to refresh a PivotTable?
Use pt.RefreshTable, where pt refers to the PivotTable object. For example, get it from a worksheet with Set pt = ws.PivotTables("SalesPivot"), then call pt.RefreshTable.

How do I refresh all PivotTables in Excel VBA?
Loop through each worksheet in ThisWorkbook.Worksheets, then each table in ws.PivotTables, and call RefreshTable. This refreshes PivotTables, not necessarily every query or connection in the workbook.

Why does my PivotTable not include new rows after VBA refresh?
The source may be a fixed range that does not cover the added rows. Use an Excel Table as the PivotTable source, or update the PivotTable’s source range separately, then refresh it.

Can Excel for the web run a PivotTable VBA macro?
No. Excel for the web can open and work with many workbook features, but VBA macros must be run in desktop Excel. On a Mac, use the desktop version of Excel for Mac and its Visual Basic Editor.

Can I undo a PivotTable refresh with Ctrl+Z?
Generally, no. VBA macro actions cannot be undone with Ctrl+Z. Save a copy before running a macro, especially if it changes more than a PivotTable refresh.

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.