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.RefreshTablewhen a macro should update a specific PivotTable and leave unrelated workbook data alone. - All workbook reports and external ranges: Use
ThisWorkbook.RefreshAllwhen 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
PivotTablescollection 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
- Conditional Formatting in Microsoft Project: What Works
- Data Validation and Verification in Excel Explained
- Power Query Replace Text: Steps, Options, and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Text Function: Format Values and CellsIn Excel VBA, use the Format function—usually Format$—to turn a number or date into a formatted string; Excel’s…
- VBA MsgBox OK Cancel: Button Options and VBA ExamplesYes. In VBA, use vbOKCancel as the buttons argument of MsgBox, then check whether the function returned vbOK…
- UserForm VBA Command Button Design OptionsUserForm VBA command buttons can be designed through their captions, size, position, colors, fonts, pictures, keyboard behavior, and…
- VBA Range Object: How to Work with CellsThe Excel VBA Range object represents one or more cells on a worksheet, letting code read or change…
- VBA MsgBox Yes/No: Continue If the User Clicks YesUse VBA’s MsgBox with the vbYesNo button option, save the result, and put the work that should continue…
- Excel VBA Open Workbook Read Only: Code and Key TipsYes. In Excel VBA, pass ReadOnly:=True to Workbooks.Open to open a workbook without saving changes back to its…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.