Yes. To make an Excel VBA pivot table adapt when records are added, put the source data in an Excel Table, create the pivot cache from that table, and refresh or rebuild the PivotTable with VBA. The example below creates a summary from a table named SalesData, so its source can grow without manually changing a fixed cell range.
What a dynamic pivot table means
A PivotTable summarizes source records into totals, counts, averages, or other views. A VBA-built pivot is not automatically dynamic just because a macro created it: the important question is whether its source includes new records and whether the pivot is refreshed after the source changes.
For example, a report might show sales by region and product. If the original source was A1:C500, adding a record in row 501 does not necessarily put that record into the existing pivot cache. The report can continue showing the old total even though the worksheet contains the new sale. A source based on an Excel Table expands as rows are added, and a refresh then updates the pivot from the expanded table.
These are two separate operations:
- Expand the source: include new records in the source data definition.
- Refresh the pivot: recalculate the summary using the current source.
Refresh alone cannot fix a source that still points to a short, fixed range. Likewise, an expanding source does not guarantee the displayed values update until the PivotTable is refreshed.
Prepare the source data as an Excel Table
On Windows in Microsoft 365, click a cell in the source data and choose Insert > Table, or press Ctrl+T. Confirm that the range includes the header row and select My table has headers. With the table selected, open Table Design and set its Table Name to SalesData. Table names cannot contain spaces; use a short, descriptive name.
Keep the source rectangular and consistent. Each column needs one header, headers must be unique, and each row should represent one record. Avoid blank header cells, merged cells, and subtotal rows inside the source. In the example macro, the headers must be exactly Region, Product, and Amount. If your workbook uses different field names, replace those names in the code.
When a new record is entered directly below the table, Excel normally expands the table to include it. Pasting a block below the table can also expand it, but check the table boundary if the paste leaves records outside. A record outside the named table is outside the pivot source too.
Build the pivot table with VBA
This macro assumes a worksheet named Data contains the SalesData table and its Region, Product, and Amount columns. It also assumes a worksheet named Pivot is available for the report. The macro removes a prior pivot with the same name on that report sheet, creates a fresh cache based on the table name, and lays out a new report beginning at A3.
Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. In the Windows desktop app, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the code. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Excel for the web can store a macro-enabled file but cannot run VBA; use the desktop app. On Mac, use Tools > Macro > Visual Basic Editor to access VBA; keyboard shortcuts can differ by keyboard and Excel settings.
Option Explicit
Sub BuildSalesPivot()
Dim wb As Workbook
Dim wsData As Worksheet
Dim wsOut As Worksheet
Dim lo As ListObject
Dim pc As PivotCache
Dim pt As PivotTable
On Error GoTo CleanFail
Application.ScreenUpdating = False
Set wb = ThisWorkbook
Set wsData = wb.Worksheets("Data")
Set wsOut = wb.Worksheets("Pivot")
Set lo = wsData.ListObjects("SalesData")
If lo.DataBodyRange Is Nothing Then
Err.Raise vbObjectError + 1000, , "SalesData has no records."
End If
' Remove the previous report, if it exists.
On Error Resume Next
wsOut.PivotTables("SalesPivot").TableRange2.Clear
On Error GoTo CleanFail
' Use the Excel Table name as the cache source.
Set pc = wb.PivotCaches.Create( _
SourceType:=xlDatabase, SourceData:=lo.Name)
Set pt = pc.CreatePivotTable( _
TableDestination:=wsOut.Range("A3"), _
TableName:="SalesPivot")
With pt
.PivotFields("Region").Orientation = xlRowField
.PivotFields("Product").Orientation = xlColumnField
.AddDataField .PivotFields("Amount"), _
"Total Amount", xlSum
.DataFields(1).NumberFormat = "$#,##0.00"
.RefreshTable
End With
Application.ScreenUpdating = True
Exit Sub
CleanFail:
Application.ScreenUpdating = True
MsgBox "Pivot build failed: " & Err.Description, vbExclamation
End Sub
The dollar format is only a display choice; change it if the Amount values use another currency or are not monetary. The macro does not create missing worksheets or the source table. Create them first, and ensure the destination area is clear of unrelated content so the PivotTable can expand.
How the macro works
The ListObjects collection identifies Excel Tables on a worksheet, and ListObjects(“SalesData”) retrieves the named table. The PivotCaches.Create method creates a cache using the table name as its source. The CreatePivotTable method places a PivotTable on the report sheet. The row and column orientations determine how Region and Product are arranged; AddDataField summarizes Amount using xlSum.
The code clears an existing report with the name SalesPivot before creating another one. That makes repeat runs useful after data changes, but it also means the macro rebuilds the layout rather than preserving manual changes to the pivot. If users rearrange fields or add filters by hand, consider a refresh-only macro instead, or deliberately define and reapply the desired layout in code.
The error handler restores screen updating and displays an explanation if a required sheet, table, field, or destination is missing. It is not a substitute for checking the workbook setup: for instance, a header named Sales Amount will not match the code’s Amount field.
Refresh an existing pivot instead of rebuilding it
If the PivotTable already uses the table as its source and its layout should remain intact, refresh it rather than delete and recreate it. For a single pivot table named SalesPivot on the Pivot worksheet, a small procedure can call its RefreshTable method. For multiple pivots that share a cache, refreshing the cache can update them together. A workbook-level refresh is another option when the file also contains external connections, though it may refresh more than the pivot.
To automate refresh after a macro changes the source rows, place the refresh call after the data update. To refresh when a user opens the file, workbook event code can be placed in the ThisWorkbook module, but opening-event macros require macros to be enabled and can affect opening time. Do not assume that a pivot will update immediately whenever someone edits a source cell; PivotTables commonly require an explicit refresh or an enabled refresh setting.
To refresh manually in Excel for Windows, click inside the PivotTable and choose PivotTable Analyze > Refresh. Refresh All is available from Data > Refresh All and can update other workbook connections as well. On Mac, the PivotTable and refresh commands are available in desktop Excel, although ribbon placement can vary by release. Excel for the web has PivotTable features, but it does not execute the VBA procedures described here.
Choose a source that fits the workbook
An Excel Table is usually the simplest source for a growing list maintained in the same workbook. It expands as records are added and gives the source a readable name. It also avoids formulas or VBA code that must calculate the final row number. The table must still contain the records, and a pivot refresh is still needed to update the summary.
A fixed range can be appropriate when the input size is genuinely fixed, but it is easy to outgrow. VBA can calculate the last used row and build a range address at run time, yet that approach needs careful handling of blank rows, multiple columns, and the correct header row. Dynamic named ranges are another possibility, but they add formula and maintenance complexity. For a normal, expanding worksheet list, a Table is often easier to inspect and maintain.
If the data comes from a database, Power Query, or another external connection, the best refresh sequence may be to refresh the connection first and then refresh the PivotTable. Check whether the pivot source is the loaded table or an external connection, because those sources behave differently from an ordinary worksheet range. Large models may also use the Data Model, which has its own setup and VBA considerations.
Common problems and practical fixes
- New rows are missing: Verify that they are inside the SalesData table, then refresh the pivot. If the source was originally a fixed range, update or recreate the cache using the table.
- Field not found: Compare the source header character for character with the field name in the macro. Extra spaces, changed labels, or duplicate headers can prevent a match or make a field ambiguous.
- Cannot create the PivotTable: Check that the Pivot sheet exists and the output area is not occupied by other values or another report. A PivotTable needs room to expand.
- Amount appears as Count: Check that the Amount column contains numeric values rather than numbers stored as text or mixed text and numbers. Correct the source data, then refresh.
- Macro does not run: Use desktop Excel, save in a macro-enabled format, and follow your organization’s macro security policy. A blocked macro or a workbook opened in a browser will not run this procedure.
- Changes cannot be reversed: Pivot and source edits made by VBA are not restored with Ctrl+Z. Keep a backup and test changes on a copy before using the macro on an important workbook.
Rebuilding versus refreshing
Rebuilding is useful when the source definition or pivot layout must be reset predictably. It can also make a small example straightforward: clear the old report, create a fresh cache, and set the fields again. The tradeoff is that manual layout changes, filters, formatting, and other pivot-specific choices may be lost unless the code recreates them.
Refreshing is preferable when the source is already correct and the existing report’s field arrangement should remain. It is usually less disruptive, but it cannot repair a cache that excludes new rows. A useful decision rule is: first confirm that the source includes the data; then choose refresh if the cache and layout are right, or rebuild if the source or configuration needs to change.
Frequently Asked Questions
How do I make a PivotTable automatically include new rows?
Convert the source range to an Excel Table and create the PivotTable from that table. New rows included in the table become part of its source; refresh the PivotTable to update the displayed summary.
Does a PivotTable refresh automatically when I add data?
Not necessarily. An expanding Table addresses the source boundary, but the PivotTable may still need Refresh or Refresh All. You can run a VBA refresh after your import or data-entry macro.
Can VBA create a PivotTable from a changing range?
Yes. VBA can calculate the range and use it as the cache source, but a fixed-size address can omit later records. For a regularly growing list, a named Excel Table is generally simpler and less error-prone.
Can I run an Excel VBA pivot macro in a browser?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac, subject to the workbook’s macro security settings.
Why does my pivot show Count instead of Sum?
Excel may treat the source field as nonnumeric if it contains text, blanks mixed with text, or numbers stored as text. Clean and convert the source values to numbers, then refresh and set the value field to Sum if needed.
Reliable workflow for a dynamic report
For a maintainable VBA pivot, keep the source in a clearly named Excel Table, use stable and unique headers, and keep the report on a separate worksheet. Decide whether the macro should refresh an established report or rebuild its layout, and make that behavior explicit. Finally, test with a newly added record and verify both that the table expands and that the displayed totals change after the macro runs. That simple check catches the most common confusion: a dynamic source and a refreshed PivotTable are related, but they are not the same thing.
Related
- Excel Split Cell by Space Delimiter: Methods and Tips
- Excel Split Cell Content Into Columns: Methods That Work
- Excel Split Cell Contents by Delimiter
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Format Date Values: Examples and FixesTo format a date in an Excel cell with VBA, set the cell’s NumberFormat property, for example: Range("A1").NumberFormat…
- VBA Range Filter: How to Use AutoFilterTo filter a worksheet range with VBA in Excel for Microsoft 365 on Windows, call the range’s AutoFilter…
- How to Use Macros on SteelSeries Keyboards and MiceTo use a macro on a SteelSeries keyboard or mouse, open SteelSeries GG, select the device in Engine,…
- Excel Macro Error? Common Causes and FixesAn Excel macro error usually means VBA is blocked, the code cannot find an expected workbook, worksheet, or…
- Excel VBA Arrays: What They Contain and How to Use ThemAn Excel VBA array contains an ordered set of elements that a macro can access by index; those…
- VBA Apply AutoFilter: Examples and Common FixesTo apply an AutoFilter with VBA in Excel for Microsoft 365 on Windows, call the AutoFilter method on…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.