Pivot Table Refresh From VBA: Code and Common Fixes

VBA & macros · Updated November 18, 2026

To refresh a PivotTable with VBA in Excel for Microsoft 365 on Windows, call its RefreshTable method, such as Worksheets("Summary").PivotTables("ptSales").RefreshTable. To refresh all workbook connections and PivotTables, use ThisWorkbook.RefreshAll; choose the method that matches whether you want one report or the whole workbook updated.

What a PivotTable refresh does

A PivotTable summarizes data held in a PivotCache, which is a stored representation of its source. Refreshing tells Excel to check the source and update that stored data and the report built from it. This is why changing cells in a source list does not always change the PivotTable immediately: the report can continue to show the cache’s previous contents until it is refreshed.

Refreshing is different from recalculating formulas. Pressing F9 recalculates formulas but does not, by itself, refresh a PivotTable’s source data. Likewise, refreshing a PivotTable does not necessarily fix a source range that excludes newly added rows. The report must point to the right source before a refresh can include the new records.

Excel can let multiple PivotTables share one cache. That can reduce duplicated stored data and make reports consistent, but it also means that refreshing one report may update other reports using the same cache. If a workbook has many PivotTables, identify whether they share a cache before assuming each report operates independently.

Refresh one PivotTable with VBA

Use this pattern when you know the worksheet and PivotTable names. Replace Summary with the worksheet tab name and ptSales with the PivotTable’s name. These are not necessarily the same as the visible report title or its source table name.

Save a copy of the workbook before running a macro. VBA actions generally cannot be reversed with Ctrl+Z, so a backup is the safe way to recover if the macro changes something unexpected.

Option Explicit

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

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

    ' Update this report from its source cache.
    pt.RefreshTable
End Sub

In Excel for Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, and paste the procedure there. Run it from the editor with the cursor inside the procedure and F5, or return to Excel and run it from Developer > Macros. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.

Excel for Mac also supports VBA, but its interface and shortcut behavior can vary with the keyboard and function-key settings. Open the editor from Tools > Macro > Visual Basic Editor, or use Option+F11; some keyboards require Fn+Option+F11. A workbook containing VBA must be saved in a macro-enabled format such as Excel Macro-Enabled Workbook (*.xlsm), rather than ordinary .xlsx.

Refresh all workbook data and reports

When the goal is to update external connections as well as PivotTables, RefreshAll is the workbook-level method. It can also refresh other refreshable data, so do not use it if the macro should touch only one report. The following procedure asks Excel to refresh all workbook data and then waits for pending asynchronous OLE DB or OLAP queries before recalculating formulas.

Save a copy of the workbook before running this macro, for the same reason: macro changes cannot be undone with Ctrl+Z.

Option Explicit

Sub RefreshWorkbookData()
    ' Refresh workbook connections and PivotTables.
    ThisWorkbook.RefreshAll

    ' Wait for pending OLE DB and OLAP queries.
    Application.CalculateUntilAsyncQueriesDone

    ' Recalculate formulas after the refresh.
    Application.Calculate
End Sub

RefreshAll can start background refreshes. The wait method shown applies to pending OLE DB and OLAP queries; it should not be treated as a universal signal that every possible third-party or custom refresh process has finished. If another procedure depends on a connection’s completion, check that connection’s behavior and configure or monitor it appropriately rather than assuming that a fixed pause will always work.

Make sure new rows are in the PivotTable source

A common surprise is that the macro runs successfully but the PivotTable still omits newly appended records. A refresh updates the cache from the source the PivotTable already knows about; it does not automatically repair every fixed source range. For example, a report based on A1:D500 will not include a record entered on row 501 merely because the macro refreshed it.

For regularly growing data, format the source as an Excel Table before creating the PivotTable. Select a cell in the source and choose Insert > Table, confirm whether the data has headers, and create or repoint the PivotTable to that table. Excel Tables expand when rows are added directly below them, so a later refresh can include those records without manually extending a fixed cell address. Check that new rows are actually part of the table, especially when data is pasted with blank rows or placed elsewhere.

If the source is a fixed range, use PivotTable Analyze > Change Data Source to choose the correct range or table, then refresh. The ribbon tab appears when the PivotTable is selected. For external data or a Data Model report, the source may be a connection rather than a worksheet range; changing a cell address is not the right fix in that case.

Choose the right refresh method

  • One known report: call RefreshTable on that PivotTable. This keeps the macro’s scope narrow.
  • All workbook connections and reports: call ThisWorkbook.RefreshAll. Use it when a workbook-wide update is intended.
  • One cache used by several reports: refresh a report that uses the cache and verify the linked reports. Avoid needlessly refreshing each linked PivotTable in a loop.
  • Power Query or another connection feeds the report: refresh the connection and allow its data load to complete before relying on the PivotTable’s results. Refreshing only the report may use data that has not yet been reloaded.

These methods are not interchangeable in every workbook. Refreshing a single PivotTable is more targeted, while RefreshAll can affect connection-backed tables, queries, and other reports in addition to the PivotTable you had in mind. Conversely, a workbook-wide refresh is useful when a report depends on a chain of imported data and summaries that all need updating.

Common errors and troubleshooting

  • “Subscript out of range”: verify the worksheet name, including spaces, and confirm that the worksheet belongs to ThisWorkbook. That refers to the workbook containing the macro; ActiveWorkbook refers to whichever workbook is active and can be a different file.
  • “PivotTable field name is not valid” or a refresh error: inspect the source headers. A source table needs a header in each column; blank or duplicated headers and merged header cells can prevent Excel from recognizing fields properly.
  • New records are missing: check the source range or table boundary before changing the VBA. A successful refresh cannot add rows that are outside the configured source.
  • Connection or credential prompt: the data source may require access, a current sign-in, or permission. A macro cannot safely make an unavailable source or missing authorization valid.
  • It works manually but not in the macro: check that the procedure targets the intended workbook and report, that macros are permitted by the organization’s security settings, and that the source is accessible at run time.

For debugging, run the procedure from the Visual Basic Editor and inspect the highlighted line if VBA stops with an error. Confirm the PivotTable’s actual name by selecting it and opening PivotTable Analyze > PivotTable Name. Avoid suppressing all errors with broad error-resume instructions while diagnosing the problem: that can hide a failed refresh and leave a workbook displaying stale numbers.

Refresh order, speed, and workbook safety

If a PivotTable is based on a query or connection, update the upstream data first and the report afterward. A refresh sequence that starts a background query and immediately reads the PivotTable can produce results based on the previous load. Where possible, use the connection’s refresh settings to control background behavior, or use an appropriate completion check for the specific connection type. A hard-coded delay, such as waiting five seconds, is unreliable because refresh duration depends on data volume, network speed, and server response.

Refreshing every report individually can also waste time when several reports share a cache. Prefer the narrowest method that meets the task, and do not combine RefreshAll with a second blanket loop unless there is a specific reason. Large source data, external connections, calculated fields, and many distinct caches can all add processing time.

Macros can refresh data, but they do not bypass workbook security or source permissions. Only enable macros in files you trust, and follow your organization’s rules for downloaded files and signed code. Save the workbook as .xlsm if it needs to retain the macro; an .xlsx copy cannot store VBA code.

Frequently Asked Questions

Can I refresh a PivotTable automatically when the workbook opens?
Yes. A workbook can use a Workbook_Open event procedure to call a refresh routine, but the file must be macro-enabled and the user’s security settings must permit the code. Consider that an opening refresh can take time or prompt for credentials, and test the workbook with the intended data connection.

Does F9 refresh a PivotTable?
No. F9 recalculates formulas; use the PivotTable refresh command or VBA’s RefreshTable method to update a PivotTable cache. If formulas depend on the refreshed results, recalculation may be useful after the data refresh.

Why does RefreshTable not include rows I just added?
The PivotTable may use a fixed source range that ends before those rows. Extend or change the source, or use an Excel Table as the source so that it can expand with appended records, then refresh again.

Is RefreshAll the same as refreshing one PivotTable?
No. RefreshAll requests refreshes for workbook data connections and PivotTables, while RefreshTable targets one PivotTable report. Use RefreshAll only when updating the workbook’s broader set of refreshable data is intended.

Can the macro refresh a PivotTable in Excel for the web?
Excel for the web can open and work with many macro-enabled workbooks, but it cannot run VBA macros. Run the macro in desktop Excel for Windows or Mac; web-based refresh options depend on the workbook’s data source and available Excel 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.