To change a PivotTable’s source with VBA, create a PivotCache for the new worksheet range or Excel table, assign it with PivotTable.ChangePivotCache, and refresh the PivotTable. The examples below show both approaches for Excel for Microsoft 365 on Windows; save a copy of the workbook first, because macros cannot be undone with Ctrl+Z.
What changing a PivotTable source actually does
A PivotTable displays a summary built from a data source held in a PivotCache. If the source range has moved, grown, or been replaced, refreshing the existing PivotTable may not be enough: refresh reads from the cache’s current source. To point the PivotTable somewhere else, VBA generally creates a cache using the new source and assigns that cache to the PivotTable.
The key object-model steps are:
- Identify the worksheet range or Excel table that contains the source data.
- Create a workbook PivotCache with
Workbook.PivotCaches.Create. - Use
PivotTable.ChangePivotCacheto assign it. - Refresh the PivotTable so the displayed results reflect the new data.
This changes the source, not the PivotTable’s design. Existing row, column, filter, and value fields are usually retained if the new data has matching field names. If the new source uses different column headings, fields may be missing or the layout may need repair.
Prepare the source before running a macro
Make the source a simple rectangular dataset: one header row, one record per row, and one field per column. Avoid blank header cells, merged headings, and completely blank rows or columns inside the data. PivotTables use the header names as field names, so a missing or ambiguous heading can prevent a cache from being created correctly.
Before you run the macro, confirm these details:
- The workbook and worksheet names in the code match the file.
- The PivotTable name is correct. Select the PivotTable and check its name on the PivotTable Analyze tab, in the PivotTable Name box.
- The new data includes the same field names if you want to preserve the existing layout.
- The workbook is saved as a macro-enabled file, such as
.xlsm, if you need to retain the code.
For Windows, open the VBA editor with Alt+F11, choose Insert > Module, and paste in the macro. You may need to enable the Developer tab or allow macros under your organization’s security policy. Excel for the web can open a workbook containing VBA, but it cannot run VBA macros. Excel for Mac supports VBA, though the editor shortcut can vary with the keyboard; use Developer > Visual Basic if the shortcut differs.
VBA example: change the source to a worksheet range
This example uses the connected data region beginning at cell A1 on a worksheet named Data. It assigns that region to a PivotTable named PivotTable1 on a worksheet named Report. Change those names to match your workbook. The external R1C1 address identifies the source worksheet and workbook as well as its boundaries.
Sub ChangePivotSourceToCurrentRegion()
Dim wb As Workbook
Dim wsData As Worksheet
Dim wsReport As Worksheet
Dim sourceRange As Range
Dim pt As PivotTable
Dim pc As PivotCache
Dim col As Long
Set wb = ThisWorkbook
Set wsData = wb.Worksheets("Data")
Set wsReport = wb.Worksheets("Report")
Set pt = wsReport.PivotTables("PivotTable1")
' Use the contiguous data block starting at A1.
Set sourceRange = wsData.Range("A1").CurrentRegion
' A PivotTable needs a header row and at least one data row.
If sourceRange.Rows.Count < 2 Then
Err.Raise vbObjectError + 1000, , "Source needs a header and data row."
End If
' Check that every source column has a heading.
For col = 1 To sourceRange.Columns.Count
If Len(Trim$(CStr(sourceRange.Cells(1, col).Value))) = 0 Then
Err.Raise vbObjectError + 1001, , "A source header is blank."
End If
Next col
' Build a cache from the new range, then assign and refresh it.
Set pc = wb.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=sourceRange.Address( _
ReferenceStyle:=xlR1C1, External:=True))
pt.ChangePivotCache pc
pt.RefreshTable
End Sub
CurrentRegion is convenient when the source is a continuous block with no fully blank row or column dividing it. It can include unwanted adjacent content, or stop early if a blank row separates records. If your data does not have a dependable boundary, replace the CurrentRegion line with an explicitly chosen range, such as wsData.Range("A1:F5000"), or use an Excel table as shown next.
VBA example: use an Excel table as the source
An Excel table is often the better source for data that grows over time. New rows added to the table become part of the table automatically. The PivotTable still needs a refresh to display those rows, but you do not have to calculate a new last row or edit a fixed address each month.
Format the source as a table with Insert > Table, then give it a clear name on the Table Design tab, such as SalesData. In the following macro, the table is on Data and the PivotTable is on Report:
Sub ChangePivotSourceToTable()
Dim wb As Workbook
Dim wsData As Worksheet
Dim wsReport As Worksheet
Dim sourceTable As ListObject
Dim pt As PivotTable
Dim pc As PivotCache
Set wb = ThisWorkbook
Set wsData = wb.Worksheets("Data")
Set wsReport = wb.Worksheets("Report")
Set sourceTable = wsData.ListObjects("SalesData")
Set pt = wsReport.PivotTables("PivotTable1")
' Create a cache from the table name.
Set pc = wb.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=sourceTable.Name)
pt.ChangePivotCache pc
pt.RefreshTable
End Sub
The table name is not necessarily the same as the worksheet name. Check it under Table Design > Table Name. If your Excel setup or source arrangement does not accept the table name as the cache source, use the table’s range address with sourceTable.Range.Address(ReferenceStyle:=xlR1C1, External:=True); however, an address represents the current size rather than the table’s continuing identity. Keep the refresh step either way.
Changing one PivotTable or several
The examples change one named PivotTable. For several PivotTables, repeat the cache assignment for each one that should use the new source. You can create the cache once and reuse the pc variable in a loop, provided the PivotTables belong to the same workbook and the new source is appropriate for all of them. Check field names before changing multiple reports: a source with different headings may leave some PivotTables with unavailable fields.
PivotTables can share a cache. Changing one PivotTable to a newly created cache does not mean every other PivotTable in the workbook should be assumed to have moved to the new source. Explicitly identify which reports need the change, then verify each one. If preserving shared-cache behavior matters for workbook size or coordinated filtering, review the cache relationships after the change rather than creating separate caches without a reason.
Refresh, save, and verify the result
ChangePivotCache assigns a cache; RefreshTable asks the PivotTable to update its displayed results. A successful macro run is not proof that the summary is correct, so inspect the result and the source boundaries. For example, check that the final data row appears in the PivotTable and that its grand total agrees with the underlying records.
You can also refresh all PivotTables in a workbook when that is the intended action, but avoid doing so automatically if the workbook contains unrelated reports or slow external connections. A manual alternative in Excel for Microsoft 365 on Windows is to select the PivotTable and choose PivotTable Analyze > Refresh. To refresh every PivotTable, use Data > Refresh All. These refresh commands do not by themselves change a fixed source range into a larger one.
After confirming the output, save the workbook. If the macro changes a report used by other people, keep a backup or test the procedure on a copy first. VBA changes cannot be reversed with Ctrl+Z, and a macro can update workbook content before a later line encounters an error.
Common problems and how to diagnose them
- Subscript out of range: A worksheet, table, or PivotTable name in the code does not match the workbook. Check spelling, spaces, and the PivotTable name box.
- New rows do not appear: A fixed range may still end above the new records. Expand the range or use an Excel table, then refresh.
- Fields disappear or the layout changes: The replacement data has different headers. Match the old field names where possible, or update the PivotTable fields intentionally.
- Blank or duplicate headings cause trouble: Give every column a nonblank, distinct heading. A source must have usable field names.
- The PivotTable uses an external or Data Model source: The examples are for worksheet data represented by an
xlDatabasecache. OLAP, Power Pivot/Data Model, and some external connection PivotTables use different connection mechanisms;ChangePivotCacheis not a universal way to retarget them. - The macro is blocked: Excel’s macro settings or an organization’s security controls may prevent execution. Do not enable macros from an untrusted workbook just to run a source-change procedure.
When to use the ribbon instead of VBA
For a one-time edit, the ribbon may be simpler. Select the PivotTable and choose PivotTable Analyze > Change Data Source, then select the new table or range and confirm. This is useful when you want to inspect the chosen range visually or do not need to repeat the task.
VBA is more useful when a recurring process imports or replaces source data, updates several reports, or must run as part of a larger macro. If the source grows regularly, a table plus a refresh may be easier to maintain than repeatedly calculating and assigning a new range. Neither approach removes the need to check that the PivotTable is summarizing the intended records.
Frequently Asked Questions
Can I change a PivotTable source without rebuilding the PivotTable?
Usually, yes. Create a cache for the new worksheet source, assign it with ChangePivotCache, and refresh. The existing layout can remain if the replacement source contains compatible field names.
Does refreshing a PivotTable automatically include new rows?
Only if its source includes those rows. A fixed address does not necessarily expand when records are appended. An Excel table expands as rows are added, but the PivotTable still needs to be refreshed.
Can I change the source for a PivotTable connected to the Data Model?
Not with the worksheet-range examples here. Data Model and OLAP PivotTables use connection-based sources, so their source needs to be changed through the relevant model or connection workflow.
Will changing one PivotTable’s source change every PivotTable?
Do not assume that it will. Some PivotTables share a cache, but assigning a newly created cache to one PivotTable is not a reliable substitute for deliberately updating and checking every report that should change.
Can I run this macro in Excel for the web?
No. Excel for the web can preserve a workbook that contains VBA, but it does not run VBA macros. Use the desktop Excel app to execute the code.
Related
- Power Query Circular Reference: Causes and Fixes
- Excel Data Validation Source: Lists, Ranges, and Rules
- Excel Data Validation Rules: How They Work and When to Use Them
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Range Find VBA: Search Cells ReliablyIn Excel VBA, use the Range.Find method to search a specified range; check whether it returned a cell…
- Excel VBA: Delete a Range of Rows by Number or MatchTo delete worksheet rows with Excel VBA, use Rows("3:7").Delete for rows 3 through 7, or qualify the range…
- Excel VBA: Delete a Worksheet Without a Prompt SafelyTo delete an Excel worksheet without the confirmation prompt, set Application.DisplayAlerts to False immediately before calling the worksheet’s…
- Mouse Macro Recorder: How It Works and What to ChooseA mouse macro recorder is software or a device feature that records actions such as clicks, pointer movements,…
- Import Multiple Files Using VBA in Excel: A Practical GuideYes. In Excel for Microsoft 365 on Windows, VBA can loop through files in a selected folder, open…
- VBA Range Variables: How to Declare and Use ThemA VBA Range variable is an object variable that refers to one or more cells in an Excel…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.