VBA Pivot Table Update: Refresh Tables and Reports

VBA & macros · Updated November 18, 2026

To update a PivotTable with VBA, call its RefreshTable method; to refresh every PivotTable and external data range in the workbook, use ThisWorkbook.RefreshAll. Refreshing makes the report read current source data, but it does not fix a PivotTable whose source range excludes new rows or whose connection cannot access its data.

What a PivotTable update actually does

A PivotTable summarizes data stored in a source range, an Excel Table, a workbook data model, or an external connection. The report can keep showing an earlier summary after the source changes because Excel commonly holds a PivotCache: a stored representation of the source data used by one or more PivotTables. Refreshing asks Excel to read the source again and update the cache and report.

This is different from recalculating a worksheet formula. Pressing F9 recalculates formulas, but it is not a substitute for refreshing a PivotTable. Likewise, refreshing a PivotTable does not automatically correct a source definition that stops at row 500 when new records have been added in row 501 and beyond. The refresh command and the source range both need to be right.

In Excel for Microsoft 365 on Windows, you can refresh a selected report from PivotTable Analyze > Refresh. To refresh workbook data and reports together, use Data > Refresh All. VBA provides matching methods for automating those actions.

Prepare the source before refreshing

For a recurring report, make the source data an Excel Table rather than a fixed-size cell range. Select a cell in the data and choose Insert > Table, confirm whether the data has headers, and give the table a useful name from Table Design > Table Name. A PivotTable based on that table can include rows added to the table when it is refreshed. This is usually safer than relying on a source such as A1:F500, which does not grow merely because data is typed below it.

Check the source layout as well. Each column should have a distinct, nonblank header, and each row should represent a record consistently. A missing header, merged cells in the source, or a changed column name can prevent a refresh or alter how fields appear. If the PivotTable uses Power Query, an external database, or another connection, refresh the upstream query or connection as appropriate and confirm that access credentials and permissions are still valid.

Before writing a macro, identify the worksheet and PivotTable name. Click inside the report and look at the PivotTable Analyze tab for its name, or inspect the worksheet’s PivotTables collection in the VBA editor. Names such as PivotTable1 and SalesByRegion are not interchangeable: VBA needs the actual name in the workbook.

Refresh one PivotTable with VBA

Open the Visual Basic Editor with Alt+F11 on Windows. Choose Insert > Module, then place a procedure in the module. Replace the example worksheet and PivotTable names with names from your workbook.

Option Explicit

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

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

    ' Read the current source data into the PivotTable.
    pt.RefreshTable
End Sub

RefreshTable is the direct method when the goal is to update one report. It returns a Boolean value indicating whether the refresh succeeded. For example, a macro can store that result and use it to decide whether to display a success message; the simple example above calls the method without storing the return value. A misspelled worksheet or PivotTable name causes a VBA error, so check both names before running the procedure.

In Excel for Mac, VBA is available in the desktop app; the Visual Basic Editor can be opened from the Developer tab, and the keyboard shortcut is commonly Option+F11 (some keyboards also require the Fn key). Excel for the web does not run VBA macros. Open the workbook in the desktop app to edit or run this code.

Refresh every report or workbook connection

When a workbook contains several reports or imports, use the workbook-level method rather than manually refreshing each visible report. RefreshAll initiates refreshes for external data ranges and PivotTable reports in that workbook. It is useful for a dashboard whose charts, tables, and summaries depend on the same updated data.

Option Explicit

Sub RefreshWorkbookData()
    ' Refresh workbook PivotTables and external data ranges.
    ThisWorkbook.RefreshAll
End Sub

ThisWorkbook means the workbook containing the macro, which is generally less error-prone than ActiveWorkbook. The latter can refer to a different workbook if the user switches windows before running the macro. To perform the same broad refresh manually, choose Data > Refresh All.

Some external queries run asynchronously, so a macro may continue while a connection is still working. Excel’s Application.CalculateUntilAsyncQueriesDone method can wait for pending OLE DB and OLAP queries. It does not guarantee that every possible source type or background process has finished, so confirm the behavior with the workbook’s specific connections before building later steps around it. For example, do not assume a downstream export is ready simply because the RefreshAll call has returned.

Refresh PivotTables on a particular worksheet

If you need to update every PivotTable on one sheet, loop through that sheet’s PivotTables collection. This can be useful when a workbook has unrelated reports that should not be refreshed by a particular button.

Option Explicit

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

    Set ws = ThisWorkbook.Worksheets("Summary")

    ' Refresh each PivotTable on the chosen sheet.
    For Each pt In ws.PivotTables
        pt.RefreshTable
    Next pt
End Sub

To adapt the procedure for another worksheet, change Summary to its tab name. A workbook-wide loop is also possible, but refreshing many reports individually can repeat work when several PivotTables share a cache. For a general workbook update, RefreshAll is often the simpler starting point. If a workbook is slow, examine its connections and caches rather than assuming that more VBA loops will make the refresh faster.

Choose the right refresh method

  • One known report: Use PivotTable.RefreshTable when a macro should update a specific PivotTable and leave unrelated workbook data alone.
  • All workbook reports and external ranges: Use ThisWorkbook.RefreshAll when the task is a broad update, such as refreshing a dashboard before saving or distributing it.
  • All reports on one sheet: Loop through that worksheet’s PivotTables collection when you need a narrower scope than the whole workbook.
  • A shared cache: Remember that multiple PivotTables can use the same PivotCache. A cache refresh can affect multiple connected reports, so check which reports share data before trying to refresh each one repeatedly.

These choices are not a ranking of speed in every workbook. Refresh time depends on source size, connection type, network access, calculations, and how many reports depend on the data. A workbook with a local Excel Table may refresh quickly, while a report that queries a remote database can take longer and may prompt for credentials.

Automate the update safely

Save a backup copy before running a macro. VBA actions generally cannot be undone with Ctrl+Z, and a refresh can change displayed results or trigger a connection prompt. Save macro code in a macro-enabled .xlsm workbook if it needs to remain available after closing the file. Only enable macros in files you trust, and follow your organization’s security rules.

A macro can be assigned to a worksheet button, or run from the Macro dialog with Alt+F8 on Windows. Avoid refreshing automatically at several points without a reason: a workbook that refreshes on opening, on a button click, and again in a separate procedure may query the same source multiple times. If the report must update at opening, select a cell in the PivotTable, open PivotTable Analyze > Options, and look on the Data tab for Refresh data when opening the file. The exact options available can depend on the PivotTable’s data source.

On Mac, save the workbook as a macro-enabled file when retaining VBA, and use the desktop version of Excel. Some Windows-specific automation components and controls are not available on Mac; the examples here use standard Excel objects and avoid those dependencies. A macro-enabled workbook can be opened in Excel for the web, but its VBA cannot be edited or run there.

Common problems and how to diagnose them

  • New records are missing: Inspect the PivotTable’s data source. If it points to a fixed range, extend or redefine that range, or base the report on an Excel Table that expands with added records. Then refresh again.
  • The macro says an item cannot be found: Verify the worksheet tab name and PivotTable name, including spaces. Use the exact names in the workbook rather than assuming the report is named after its visible title.
  • The report still shows old external data: Check whether the connection completed, whether credentials are required, and whether the source itself contains the expected records. Refreshing a PivotTable cannot make an unavailable database or query return current results.
  • Refresh fails on a protected sheet: Sheet protection can prevent Excel from changing PivotTable layout or results. Review the workbook’s protection design and permissions; do not remove protection blindly, especially in a shared file.
  • Refresh takes a long time: Large source data, remote connections, complex calculations, and repeated refreshes can all contribute. Check whether several reports share a cache and whether a single RefreshAll call is sufficient.

When diagnosing a problem, first refresh manually with Data > Refresh All or the selected report’s refresh command. If that also fails, the issue is probably with the source, connection, permissions, or PivotTable definition rather than the VBA syntax. If the manual refresh succeeds but the macro fails, check the names and the exact workbook reference used in the procedure.

Frequently Asked Questions

What VBA command refreshes a PivotTable?
Use pt.RefreshTable, where pt is a PivotTable object. For example, assign it with Set pt = ThisWorkbook.Worksheets("Summary").PivotTables("SalesByRegion"), then call pt.RefreshTable.

Does RefreshAll update PivotTables?
Yes. ThisWorkbook.RefreshAll initiates refreshes for the workbook’s PivotTables and external data ranges. External queries can take time or require access credentials, and some run asynchronously.

Why does a refreshed PivotTable not include new rows?
The report may use a fixed source range that does not include those rows. Expand the source or use an Excel Table as the source, then refresh. A refresh reads the defined source; it does not automatically rewrite an incorrect source definition.

Can I update a PivotTable automatically when the file opens?
For a supported PivotTable, select a cell in the report, go to PivotTable Analyze > Options, and enable Refresh data when opening the file on the Data tab. Alternatively, a workbook-open macro can call a refresh method, but it requires macros to be enabled.

Can VBA refresh PivotTables in Excel for the web?
No. Excel for the web can display a workbook containing VBA, but it cannot run or edit VBA macros. Use Excel for Windows or Mac desktop to run the code; the Mac version may differ for Windows-specific automation features.

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.