In VBA, a PivotTable’s source range is the worksheet data used to build or refresh it, while its output range is the area occupied by the finished report. Use a range with a header row for the source, and inspect PivotTable.TableRange2 when you need the report’s full output area.
What “PivotTable range” means in VBA
The phrase can refer to several different ranges, and choosing the right one is the first step to reliable code. A PivotTable is built from source data, stored through a PivotCache, and displayed in a worksheet as a report. Those parts are related, but they are not interchangeable.
- Source range: the cells containing field names and records, such as
Data!A1:D250. The top row should contain unique, nonblank column headings. - PivotCache source: the data reference held by the cache that supplies one or more PivotTables.
- Output range: the worksheet cells occupied by the PivotTable at its current size.
- Data body: the PivotTable’s value cells, excluding headings and report-filter areas. It may not exist when a report has no displayed data.
For a PivotTable object named pt, pt.TableRange1 returns the report range excluding page fields, while pt.TableRange2 includes them. pt.DataBodyRange identifies the body of the report. These are properties to read, not ordinary source ranges to use when creating the cache. In particular, do not assume the output range is fixed: adding fields, changing filters, or refreshing can change its size.
How the source range is used
Excel VBA typically creates a PivotCache from a worksheet range, then creates a PivotTable from that cache. The source is not simply a label attached to the report; it is the data the cache uses when it is built or refreshed. For a standard worksheet-based PivotTable, a source range should include the headings and all records, without unrelated notes or blank header cells.
A fixed address such as A1:D250 is easy to understand but can omit later records. A dynamic selection can adapt to added records, but the method matters. CurrentRegion expands to the contiguous block around a starting cell; a completely blank row or column can break that block. A structured Excel Table is usually the most dependable choice for data that grows over time.
When a PivotTable shares a cache with other reports, changing the cache source can affect those reports too. Before modifying an existing PivotTable’s source, consider whether other PivotTables depend on the same cache and whether the new data has matching field names and compatible column types.
Choosing a reliable source range
Use the source structure that matches how the workbook is maintained. These are common options:
- Excel Table: Best for a regularly updated list. Tables expand when users add rows directly below them, and their names are easier to maintain than changing cell addresses. For example, a table named
SalesTablecan be used as the source. - CurrentRegion: Convenient for a compact, contiguous data block. It is quick to use, but blank rows or columns inside the data can cause the selected range to stop early.
- Explicit range: Useful when the data boundary is known and controlled. Update the address when the dataset grows, or the PivotTable can silently exclude new rows.
- Named range: Helpful when a workbook already has a maintained name for the source. Confirm that the name refers to the intended cells before using it in the cache.
For most recurring reports, format the data as a Table using Insert > Table, confirm that the table has headers, and give it a meaningful name from the Table Design tab. Avoid merged cells in the source, blank or duplicate column headings, and totals mixed into the records. A PivotTable expects a rectangular list in which each column represents one field.
Create a PivotTable from a worksheet range
This Windows VBA example uses the contiguous data block starting at cell A1 on a sheet called Data. It expects columns named Region and Sales, and creates a report on a sheet called Report. Change the sheet and field names to match the workbook. The destination cell must not overlap another PivotTable or existing content that should be preserved.
Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z.
Sub CreateSalesPivot()
Dim wb As Workbook
Dim wsData As Worksheet
Dim wsReport As Worksheet
Dim sourceRange As Range
Dim pivotCache As PivotCache
Dim pt As PivotTable
' Use the contiguous data block that begins at A1.
Set wb = ThisWorkbook
Set wsData = wb.Worksheets("Data")
Set wsReport = wb.Worksheets("Report")
Set sourceRange = wsData.Range("A1").CurrentRegion
' Create a cache from the complete range, including its headers.
Set pivotCache = wb.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=sourceRange.Address( _
ReferenceStyle:=xlR1C1, External:=True))
' Place the report with its upper-left corner at B3.
Set pt = pivotCache.CreatePivotTable( _
TableDestination:=wsReport.Range("B3"), _
TableName:="ptSales")
' Add the example fields to the report.
With pt
.PivotFields("Region").Orientation = xlRowField
.AddDataField .PivotFields("Sales"), "Total Sales", xlSum
End With
End Sub
TableDestination is a single starting cell, not a pre-sized rectangle. Excel determines the report’s final footprint from the fields and layout. The code assumes the workbook already contains both named worksheets and that the source headings match the field names exactly. If the macro reports that a field is missing, check the spelling and the source header row first.
Use an Excel Table for growing data
A Table avoids relying on a manually updated last row. The following version uses the full range of a Table named SalesTable on the Data sheet. It creates a PivotTable at B3 on Report; as above, the required Region and Sales headings must be present.
Save a copy before running this macro as well; Ctrl+Z does not reverse VBA actions.
Sub CreatePivotFromTable()
Dim wb As Workbook
Dim wsData As Worksheet
Dim wsReport As Worksheet
Dim sourceRange As Range
Dim pivotCache As PivotCache
Dim pt As PivotTable
' The table range includes its header row and current records.
Set wb = ThisWorkbook
Set wsData = wb.Worksheets("Data")
Set wsReport = wb.Worksheets("Report")
Set sourceRange = wsData.ListObjects("SalesTable").Range
' Build the cache and place a new report at B3.
Set pivotCache = wb.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=sourceRange.Address( _
ReferenceStyle:=xlR1C1, External:=True))
Set pt = pivotCache.CreatePivotTable( _
TableDestination:=wsReport.Range("B3"), _
TableName:="ptSales")
' Configure the example row and value fields.
With pt
.PivotFields("Region").Orientation = xlRowField
.AddDataField .PivotFields("Sales"), "Total Sales", xlSum
End With
End Sub
Adding rows to the Table makes them part of its range, but it does not guarantee that every existing PivotTable has already refreshed. Refresh the report after the source data changes. The cache and report should then reflect the current Table records.
Find the current PivotTable output range
To select, format, export, or inspect a report after it has been created, retrieve its range from the PivotTable object. For example, this macro displays the address of the full report including page fields. It assumes a PivotTable named ptSales exists on the Report sheet.
Sub ShowPivotOutputAddress()
Dim ws As Worksheet
Dim pt As PivotTable
Dim outputRange As Range
' Get the PivotTable and its full displayed range.
Set ws = ThisWorkbook.Worksheets("Report")
Set pt = ws.PivotTables("ptSales")
Set outputRange = pt.TableRange2
' Show a worksheet-style address in a message box.
MsgBox outputRange.Address(External:=True)
End Sub
Use TableRange1 instead when page fields should be excluded. Use DataBodyRange when the task concerns value cells only, such as applying a number format to the values. Check whether DataBodyRange is Nothing before using it in code: a PivotTable may not currently have a data body. Also, do not store an output address as a permanent boundary if the report can change; retrieve the property again after the layout changes or refreshes.
Update the source and refresh an existing report
There are two different operations that are often both called “refreshing.” RefreshTable refreshes a report from its current cache source. It does not automatically change a fixed source address to include rows that were added outside that address. If the source is an Excel Table, newly added table rows can be included when the cache refreshes. If the source is a fixed range, update the source as well as refreshing.
For a worksheet-based PivotTable that needs a different source range, a common approach is to create a cache for the new source and assign it to the PivotTable with ChangePivotCache. This example assumes ptSales exists and the new data is a contiguous block at A1 on Data. It is intended for a standard range-based PivotTable, not an OLAP or Data Model report. If other PivotTables share the original cache, changing only this report to a new cache can separate it from them.
Save a copy of the workbook before running source-changing code; macros cannot be undone with Ctrl+Z.
Sub ChangePivotSourceAndRefresh()
Dim wb As Workbook
Dim wsData As Worksheet
Dim wsReport As Worksheet
Dim sourceRange As Range
Dim newCache As PivotCache
Dim pt As PivotTable
' Identify the new source and the existing PivotTable.
Set wb = ThisWorkbook
Set wsData = wb.Worksheets("Data")
Set wsReport = wb.Worksheets("Report")
Set sourceRange = wsData.Range("A1").CurrentRegion
Set pt = wsReport.PivotTables("ptSales")
' Create a range-based cache, switch this report, and refresh it.
Set newCache = wb.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=sourceRange.Address( _
ReferenceStyle:=xlR1C1, External:=True))
pt.ChangePivotCache newCache
pt.RefreshTable
End Sub
If the source headers have changed, the report’s field setup may need attention too. Updating a cache source cannot make an old field name continue to refer to a column that has been renamed or removed. For a routine data update with unchanged columns, refreshing the existing report is simpler than changing its cache.
Common range problems and how to avoid them
- New rows are missing: The cache may still point to a fixed range that ends above the new records. Use a Table, update the cache source, and refresh.
- The source stops too early:
CurrentRegiontreats fully blank rows and columns as boundaries. Remove unintended gaps or use a Table or another deliberate range definition. - Excel says a field name is invalid: Check that every source column has a nonblank, unique header and that the field name in VBA matches it.
- The report overwrites cells or fails to create: Choose an empty destination area and avoid placing the new report on top of another PivotTable. Excel determines the final report size, so leave room for it to expand.
- A range variable points to an unexpected sheet: Qualify references with a worksheet variable, as in
wsData.Range("A1"), rather than relying on whichever sheet happens to be active. - The refreshed report has unexpected totals: Check whether the source includes a totals row, text mixed into numeric columns, or records outside the intended range. These source-quality issues can affect grouping and aggregation.
On Microsoft 365 for Windows, VBA is available in desktop Excel; Excel for the web can display a workbook containing macros but cannot run or edit VBA. On Mac, use Excel’s Visual Basic Editor and check that the workbook and code are compatible; keyboard access can differ, and some Windows-specific automation features are unavailable. The range and PivotTable object model concepts remain the same, but do not assume a Windows-only macro will work unchanged on Mac.
Frequently Asked Questions
How do I get the range of a PivotTable in VBA?
Set a Range variable to the PivotTable’s TableRange2 property for the complete report, including page fields. Use TableRange1 to exclude those fields, or DataBodyRange for the value area.
How do I set the source range for a PivotTable?
Create a PivotCache using the desired source range, then create the PivotTable from that cache. For an existing range-based PivotTable, create a cache for the new range, call ChangePivotCache, and refresh the report.
Does RefreshTable include rows added below a fixed source range?
Not necessarily. Refreshing updates from the cache’s current source; it does not by itself extend a fixed address. Use an Excel Table or explicitly update the PivotCache source so the added records are included.
What is the difference between TableRange1 and TableRange2?TableRange1 returns the PivotTable report excluding page fields. TableRange2 includes page fields and is therefore the better choice when you need the entire displayed report.
Can I use CurrentRegion as the PivotTable source?
Yes, when the data is one contiguous block around the starting cell. Blank rows or columns can split the region, so an Excel Table is usually safer for a list that grows or may contain gaps.
Related
- Power Query Union Tables: How to Append Queries
- Data Validation: Definition, Rules, and Examples
- Excel Data Validation Date Picker: Setup and Options
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- 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…
- Excel VBA: How to Check AutoFilter StatusIn Excel VBA, check Worksheet.FilterMode to find out whether a worksheet currently has a filter applied, and check…
- Excel Macro to Generate a Chart Automatically in VBAYes. In desktop Excel for Microsoft 365 on Windows, a VBA macro can create a chart from a…
- VBA Code for UserForm Data Entry: Build a Reliable FormTo enter records through an Excel VBA UserForm, create text boxes and a combo box on the form,…
- Excel VBA: How to Delete the Current RowTo delete the worksheet row containing the active cell, use VBA’s ActiveCell.EntireRow.Delete method. For a safer macro that…
- Excel Macro Recorder: How It Works and When to Use ItExcel’s Macro Recorder captures many worksheet actions and turns them into VBA code that you can run again…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.