Refresh Pivot Table Using VBA: Code and Tips

To refresh a PivotTable with VBA in Excel for Microsoft 365 on Windows, call its RefreshTable method; to refresh every PivotTable in the workbook, loop through each worksheet and its PivotTables collection. For all workbook data connections and reports, use ThisWorkbook.RefreshAll instead. Save a copy before running any macro, because VBA actions cannot be undone with Ctrl+Z.

Refresh one PivotTable with VBA

Use this macro when you know which worksheet and report need updating. Replace the example worksheet and PivotTable names with the names in your workbook. The worksheet tab name and PivotTable name are different things: the tab name appears at the bottom of Excel, while the PivotTable name is available when the report is selected under PivotTable Analyze > PivotTable Name.

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

    ' Identify the worksheet and PivotTable to refresh.
    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("PivotTable1")

    ' Refresh the report from its PivotCache.
    pt.RefreshTable
End Sub

Paste the procedure into a standard VBA module, not into a worksheet cell. In the Visual Basic Editor, choose Insert > Module, paste the code, and change the two names as needed. The ThisWorkbook reference means the workbook containing the macro. If the macro is stored in Personal.xlsb or another file, use a reference to the intended workbook instead; otherwise, the code may look for the report in the wrong file.

RefreshTable refreshes the selected report from its underlying PivotCache and returns a Boolean value indicating whether the refresh succeeded. The sample does not display that value, so Excel simply runs the refresh. It does not change the PivotTable’s source range or add new fields to its layout.

Refresh every PivotTable in a workbook

When a workbook contains several reports, a loop avoids writing a separate refresh command for each one. This example visits every worksheet and refreshes every PivotTable it finds, including reports on hidden worksheets.

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

    ' Visit every worksheet in the workbook containing this macro.
    For Each ws In ThisWorkbook.Worksheets
        ' Refresh each PivotTable on the current worksheet.
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws
End Sub

This is a practical choice when the task specifically concerns PivotTables and you do not want to refresh unrelated external data. If multiple reports share a cache, refreshing each report this way can do more work than necessary, depending on the workbook’s design. For a small or ordinary workbook, the simple loop is often easier to understand and maintain. For a large workbook with many reports, consider whether refreshing all connections is actually required and test the timing with a copy of the file.

To limit the operation to a particular worksheet, keep the worksheet reference and loop only through its PivotTables. To refresh one report among many, use the first example. A targeted macro can reduce waiting time and avoid updating reports that a user did not ask to refresh.

RefreshAll, PivotCache, and what each method does

Excel has several related refresh operations, and they are not interchangeable in every situation:

pt.RefreshTable refreshes one PivotTable report from its cache.
wb.RefreshAll requests a refresh of the workbook’s external data ranges and PivotTable reports. It is broader than refreshing one report.
PivotCache.Refresh refreshes a particular PivotCache, the stored data structure used by one or more PivotTables.

A PivotCache can be shared by multiple PivotTables. Sharing can reduce duplicated storage and help reports use the same underlying data, but it also means that refreshing a cache may affect more than one report. If you use cache-level code, identify the intended cache carefully rather than assuming that cache number 1 always belongs to a particular report.

For a workbook-wide refresh, the basic VBA call is:

Sub RefreshWorkbookData()
    ' Request refreshes for workbook connections and PivotTables.
    ThisWorkbook.RefreshAll
End Sub

Some connections refresh in the background, so the macro can reach its next line before every external query has finished. Excel’s Application.CalculateUntilAsyncQueriesDone method can wait for pending asynchronous OLE DB and OLAP queries, but it should not be treated as a universal completion signal for every connection type or external process. If later code depends on refreshed results, confirm how that workbook’s particular connection behaves before automating the next step.

Put the macro in Excel and run it

On Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and paste the procedure. Run it from the editor with the cursor inside the procedure by pressing F5, or return to Excel and use Alt+F8 to select a macro. You can also add a button to a worksheet and assign the macro to it. The Developer tab may need to be enabled in Excel’s ribbon settings before you can use its macro controls.

Save a working copy of the workbook before running the code.
Save a macro-enabled workbook as an .xlsm file if the VBA code needs to remain in that file.
Check the worksheet tab and PivotTable names in the code against the actual workbook.
Run the macro, then verify the refreshed date or a known total in the report.

Excel for Mac supports VBA, though keyboard shortcuts and some Excel features differ. Depending on the keyboard, Option+F11 may open the Visual Basic Editor; a Function key may also be required. Excel for the web can display a workbook containing VBA but cannot run its macros, so open the file in the desktop Excel application to execute the code. Macro-enabled files and macro security settings also matter: enable macros only for files you trust.

Make sure the PivotTable source includes new rows

A frequent misconception is that refreshing automatically expands every PivotTable’s source to include newly appended records. Refreshing updates the cache from its defined source; it does not necessarily change that source definition. If the original source was a fixed range such as A1:D500 and new records were added in row 501, a refresh may still omit them.

For data maintained as a growing list, an Excel Table is usually a more reliable source than a manually bounded range. Select the source data and choose Insert > Table, confirm whether it has headers, and build the PivotTable from that table. As rows are added to the table, its range expands, and refreshing the PivotTable can then include those rows. Check that the PivotTable actually uses the table as its source; converting the data to a table later does not necessarily rewrite an existing PivotTable’s source automatically.

If the source must remain a worksheet range, update the PivotTable’s source through PivotTable Analyze > Change Data Source, then refresh. VBA can also change a PivotTable cache’s source in supported cases, but the correct method depends on the source type. OLAP and Data Model PivotTables have restrictions that do not apply to an ordinary range-based cache, so do not assume a range-based ChangePivotCache example will work for every report.

Common refresh problems and practical fixes

If the macro stops with a subscript-out-of-range error, Excel usually could not find the worksheet or workbook name used in the code. If it reports that the PivotTable name is invalid, select the report and check its name under PivotTable Analyze. Names are exact, so a misspelling or a reference to a different workbook can prevent the procedure from reaching the refresh call.

New records are missing: Check the source range or Excel Table name, then change the source if necessary before refreshing.
External data is stale: A PivotTable refresh may not be equivalent to refreshing every workbook connection. Use RefreshAll when the underlying query or connection also needs an update.
The refresh appears to finish too soon: A connection may run asynchronously. Check its background-refresh behavior and avoid assuming all query types are covered by one wait method.
A field or layout changed unexpectedly: Confirm that the source still has the expected column headers and data types. A refresh updates data; it does not guarantee the report layout remains meaningful after source schema changes.
The macro cannot run: Confirm that the file is open in desktop Excel, macros are permitted for the trusted file, and the workbook is saved in a format that retains VBA.

Blank records, duplicate headers, changed column names, and mixed data types can also cause confusing results. For example, if a source heading is renamed, the PivotTable may no longer have the field its layout expects. Fix the source structure and inspect the report’s field list rather than repeatedly running the same refresh macro.

Choosing between a manual refresh and VBA

For an occasional update, the Excel interface is simpler: select a PivotTable and choose PivotTable Analyze > Refresh, or choose Data > Refresh All when workbook connections and reports should all update. The right-click menu on a PivotTable also offers Refresh. These commands are useful for checking that the report refreshes correctly before automating the task.

VBA is helpful when a refresh is part of a repeatable workflow, such as a button-operated monthly report or a sequence that updates several known PivotTables. It can make the operation consistent, but it also adds maintenance: worksheet names, source definitions, connection behavior, and macro permissions can change. If the task is only one click and does not need custom logic, the ribbon command may be the safer and clearer choice.

Another option is to set a PivotTable to refresh when the workbook opens. Select the PivotTable, open PivotTable Analyze > Options, and use the Data tab in the PivotTable Options dialog to find the refresh-on-open setting. The exact dialog presentation can vary with Excel updates and report type. This option is useful when the workbook should update on opening, but it can increase opening time and may require access to the external source.

Safety, performance, and automation considerations

Macros can make changes that are not recorded as a reversible editing step. Ctrl+Z cannot undo a VBA refresh or other macro action, so save a separate copy before testing. Keep code limited to the workbook and reports it needs to affect, particularly if a workbook contains slow queries, large caches, or connections that require credentials.

For larger workbooks, avoid refreshing the same shared data repeatedly if one carefully chosen cache or workbook-level refresh is more appropriate. Conversely, do not replace a targeted refresh with RefreshAll without checking what else it will update. External connections may contact databases or files, prompt for credentials, or take time to complete. A macro does not bypass access permissions or repair a broken connection.

When automating a recurring process, test with representative data and verify more than whether the macro runs without an error. Compare a known total, confirm the newest expected date appears, and check that all required PivotTables updated. If the workbook is used by other people, document the required source, macro location, and refresh scope so that future edits do not silently invalidate the procedure.

Frequently Asked Questions

How do I refresh all PivotTables in Excel using VBA?
Loop through each worksheet in the target workbook and each worksheet’s PivotTables collection, calling RefreshTable for every report. Use ThisWorkbook only when that is the workbook containing the macro.

Does RefreshTable update the source data range?
No. It refreshes the report from its defined cache and source. If new rows fall outside a fixed source range, change the source or use an expanding Excel Table, then refresh.

What is the difference between RefreshTable and RefreshAll?
RefreshTable targets one PivotTable. RefreshAll requests refreshes for workbook connections and PivotTable reports, so it may update more data and take longer.

Can I refresh a PivotTable automatically when the workbook opens?
Yes. PivotTable Options includes a refresh-on-open setting for supported reports. Alternatively, workbook-open VBA can call a refresh method, but any external connection’s access and completion behavior still apply.

Can Excel for the web run a PivotTable refresh macro?
No. Excel for the web does not execute VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to run the code.

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.