To change an Excel PivotTable’s source with VBA, create a new PivotCache from the replacement range, assign it with PivotTable.ChangePivotCache, then refresh the table. The macro below shows the pattern for a PivotTable based on worksheet data in Excel for Microsoft 365 on Windows.
How a PivotTable data source works
A PivotTable does not normally read and summarize the worksheet cells directly every time you view it. Excel builds a PivotCache from the source data, and the PivotTable displays a summary based on that cache. Refreshing a PivotTable updates the cache from its existing source; changing the source means pointing it at a different range or table and then refreshing.
This distinction matters when new rows or columns fall outside the original source. A refresh alone may not include them. In that case, change the cache’s source first, or use an Excel Table that expands as rows are added and refresh the PivotTable afterward. VBA can automate either approach.
VBA example: replace the source range
Assume the source data is a contiguous block with headers on a worksheet named Data, and the PivotTable is named PivotTable1 on a worksheet named Report. The example uses CurrentRegion to find the block around cell A1, creates a new cache, assigns it to the PivotTable, and refreshes it.
Sub ChangePivotSource()
Dim wsData As Worksheet
Dim wsReport As Worksheet
Dim sourceRange As Range
Dim pt As PivotTable
Dim pc As PivotCache
' Identify the source and report sheets in this workbook.
Set wsData = ThisWorkbook.Worksheets("Data")
Set wsReport = ThisWorkbook.Worksheets("Report")
Set sourceRange = wsData.Range("A1").CurrentRegion
Set pt = wsReport.PivotTables("PivotTable1")
' Build a cache from the replacement range and update the PivotTable.
Set pc = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=sourceRange.Address( _
ReferenceStyle:=xlR1C1, External:=True))
pt.ChangePivotCache pc
pt.RefreshTable
End Sub
Change the sheet and PivotTable names to match the workbook. The external R1C1 address identifies both the range and its worksheet, so the source is not accidentally interpreted as a same-address range on the report sheet. ThisWorkbook means the workbook containing this VBA code; it is not necessarily the workbook currently active on screen.
Run the macro safely
- Save a separate copy of the workbook before running the macro. VBA actions cannot be reversed with Ctrl+Z.
- Check that the source sheet has one header row and a continuous data block beginning at A1. Confirm the PivotTable’s actual name in Excel if necessary.
- Press Alt+F11 on Windows to open the Visual Basic Editor. Choose Insert > Module, paste the code, and return to Excel.
- Run the macro from the editor, or use Developer > Macros, select
ChangePivotSource, and choose Run. If Developer is not visible, enable it through File > Options > Customize Ribbon. - Check the PivotTable and its filters after the macro finishes. If the new source has different fields, the PivotTable layout may need to be adjusted.
Macros must be permitted by the workbook’s security settings. Only enable or run VBA from files and sources you trust; do not lower security settings simply to run an unfamiliar macro.
Use an Excel Table for changing row counts
CurrentRegion is convenient for a compact data block, but it stops at blank rows or columns. If a report’s data grows regularly, format the source as an Excel Table with Insert > Table, and give it a clear name under Table Design > Table Name, such as SalesTable. Tables expand when data is added directly below them, making them a more dependable source than a fixed address.
You can use the table’s current range in the macro. Replace the line that sets sourceRange with the following line; keep the cache creation and assignment code unchanged.
Set sourceRange = wsData.ListObjects("SalesTable").Range
This finds the table by name on the worksheet represented by wsData. If rows have been added to the table, the range includes them. You still need to run the macro (or otherwise refresh the PivotTable) to update the summary. The table does not make a PivotTable refresh itself automatically.
Choose the right source range
The source range must include the field headers and all rows to summarize. Each header should be nonblank and unique; PivotTables use those headers as field names. A source with merged cells, an extra title row, or a blank row splitting the data can cause errors or exclude records.
Pick the source method based on how the data is organized:
- CurrentRegion: Useful when a clean, uninterrupted block always starts at a known cell. It can omit records if a blank row or column breaks the block.
- Excel Table: Usually preferable for lists that grow over time. It expands as records are added, but the PivotTable still requires a refresh.
- Explicit range: Suitable when the source boundaries are fixed and known, such as a range from A1 to F500. A fixed address will not include later rows outside those boundaries unless the macro changes it.
For multiple PivotTables, consider whether they should all use the same source and cache. A workbook can contain separate caches, but that can use more memory. Conversely, PivotTables sharing a cache may share source and filtering behavior. Changing one PivotTable’s cache does not guarantee that every other PivotTable in the report will be moved to the new source; inspect and update each relevant table deliberately.
What changing and refreshing do
ChangePivotCache changes the cache used by a PivotTable that supports this operation. RefreshTable then asks Excel to update and display the PivotTable using its cache. The order is important: refreshing before replacing the source only refreshes the old source.
Changing the source can affect more than the number of records. If the new range has different field names or omits fields used in the report, the layout or filters may no longer work as expected. If field names stay consistent and only the rows change, the existing arrangement is more likely to remain useful, though it should still be checked.
For a PivotTable sourced from an external connection, the Data Model, or an OLAP source, this worksheet-range approach may not apply. Such PivotTables have connection or model-specific sources, and ChangePivotCache is not a general way to repoint them to an ordinary range. Use the connection or model’s appropriate settings instead.
Common errors and how to diagnose them
- Subscript out of range: A worksheet name or PivotTable name in the code does not exactly match the workbook. Confirm spelling, spaces, and which workbook the code refers to.
- Invalid source reference: The range may be empty, noncontiguous, or missing headers. Check the source sheet and ensure the selected address covers a valid rectangular list.
- The PivotTable still shows old results: Confirm that the macro reaches
pt.RefreshTable, then check the selected source range and refresh status. A refresh cannot include data outside the new source. - Fields are missing or the layout changes: Compare the new headers with the old field names. Update the PivotTable layout or restore the expected column names if the report relies on them.
- Method or source-type error: The PivotTable may be based on the Data Model, OLAP, or a connection rather than a normal worksheet range. Identify its source before using a worksheet-cache macro.
For debugging, step through the macro in the Visual Basic Editor with F8 and inspect sourceRange.Address. On Windows, Alt+F11 opens the editor and F8 steps through code. On Mac, use Developer > Visual Basic or Tools > Macro > Visual Basic Editor, depending on the ribbon setup; keyboard shortcuts can differ by Mac keyboard and Excel version. Excel for the web can display a workbook containing macros, but it cannot run VBA, so open the file in the desktop app to execute this code.
Frequently Asked Questions
Can I change a PivotTable source with VBA without recreating the PivotTable?
Yes, for a PivotTable based on a worksheet range, create a cache for the new source and assign it using ChangePivotCache. The existing layout may remain, but check it if the source fields changed.
Does refreshing a PivotTable include new rows automatically?
Not if the rows are outside its current source. Use an expanding Excel Table or update the source range, then refresh the PivotTable.
Can I use this macro for every PivotTable in a workbook?
No. It targets one named PivotTable. You can loop through PivotTables and assign caches, but first confirm which ones should share the new source and whether any use connections or the Data Model.
Why does ChangePivotCache fail on my PivotTable?
A common reason is that the PivotTable uses an OLAP connection or the Data Model rather than a standard worksheet range. The source must be changed through the relevant connection or model workflow.
Can I run VBA to change a PivotTable source in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in Excel for Windows or Mac desktop to run the macro; the exact menus and shortcuts can differ by platform.
Related
- Excel Text to Columns: How to Split Data Into Fields
- Excel Text to Columns: Split Cell Text at Line Breaks
- Excel Text to Columns: Find It and Split Data Correctly
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Variant: What It Is and When to Use ItIn Excel VBA, a Variant is a flexible data type that can hold different kinds of values, including…
- Excel VBA Copy Worksheet: Code, Methods, and SafetyTo copy a worksheet with Excel VBA, call the worksheet’s Copy method and specify where the duplicate should…
- Access VBA Date Functions: Dates, Times, and ExamplesAccess VBA provides built-in date and time functions for getting the current date, creating date values, adding or…
- Excel VBA Binary Strings: Text, Bytes, and BitsIn Excel VBA, a String is text, not a raw sequence of binary bytes. If you mean a…
- VBA to Save Excel Tabs as Separate FilesYes. A VBA macro can copy each visible worksheet tab into a new workbook and save it as…
- Convert an Excel Range to HTML in VBATo convert a range to a formatted HTML file in Excel for Microsoft 365 on Windows, add a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.