In Excel VBA, a PivotTable is controlled through objects such as PivotTable, PivotCache, and PivotField. A macro can create a PivotTable from a data range, arrange its fields, refresh its results, and apply formatting—useful when a report needs to be rebuilt consistently or updated on a schedule.
What a PivotTable object is
A PivotTable is an interactive summary of tabular data: it can group records, calculate totals or counts, and rearrange fields without changing the source rows. In VBA, the visible report and the data behind it are separate parts of the object model.
PivotTablerepresents the report on a worksheet. Its methods and properties control layout, fields, and refresh behavior.PivotCacheholds or references the data used by one or more PivotTables. Several reports can share a cache.PivotFieldrepresents a source field such as Region, Product, or Sales, and can be assigned to a row, column, filter, or values area.PivotFieldobjects used for calculations are sometimes called data fields. Their aggregation can be a sum, count, average, or another supported calculation.
This distinction matters when troubleshooting: changing a report’s field layout is not the same as changing its source data, and refreshing a report does not necessarily expand a fixed source range.
How VBA builds a PivotTable
The usual sequence is to identify the source, create a PivotCache, create the PivotTable at a destination cell, then assign source fields to report areas. The sample below assumes a worksheet named Data with a single header row and fields named Region, Product, and Sales. It also assumes a blank worksheet named Report. The macro finds the current data boundaries using column A and row 1, so column A must be populated for every record and the headers must be on the first row.
Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. To add this code in desktop Excel for Microsoft 365 on Windows, press Alt+F11, choose Insert > Module in the Visual Basic Editor, paste the code, and run the procedure from Excel or the editor.
Sub CreateSalesPivot()
' Build a regional sales summary from the Data worksheet.
Dim wb As Workbook
Dim wsData As Worksheet
Dim wsReport As Worksheet
Dim sourceRange As Range
Dim sourceAddress As String
Dim cache As PivotCache
Dim pt As PivotTable
Dim salesField As PivotField
Dim lastRow As Long
Dim lastCol As Long
Set wb = ThisWorkbook
Set wsData = wb.Worksheets("Data")
Set wsReport = wb.Worksheets("Report")
' Find the source boundaries; column A and row 1 must contain data.
lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
lastCol = wsData.Cells(1, wsData.Columns.Count).End(xlToLeft).Column
Set sourceRange = wsData.Range(wsData.Cells(1, 1), _
wsData.Cells(lastRow, lastCol))
' Use an external R1C1 address to identify the source workbook and sheet.
sourceAddress = sourceRange.Address(ReferenceStyle:=xlR1C1, _
External:=True)
Set cache = wb.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=sourceAddress)
' The destination area must be clear, and this name must be unique.
Set pt = cache.CreatePivotTable(TableDestination:=wsReport.Range("A3"), _
TableName:="ptSales")
With pt
.PivotFields("Region").Orientation = xlRowField
.PivotFields("Region").Position = 1
.PivotFields("Product").Orientation = xlColumnField
.PivotFields("Product").Position = 1
Set salesField = .AddDataField(.PivotFields("Sales"), _
"Total Sales", xlSum)
salesField.NumberFormat = "$#,##0.00"
End With
End Sub
The result is a matrix with regions down the rows, products across the columns, and summed sales in the body. The number format affects how values display; it does not change the underlying numbers or currency. If your field names differ, replace the quoted names in the code with the exact headers in the source. A header containing a blank cell, duplicate labels, or an unexpected spelling can prevent the PivotTable from being created or make a field reference fail.
Preparing reliable source data
PivotTables work best when the source is a simple rectangular list: one header row, one record per row, and one kind of information per column. Remove merged cells from the source area, avoid completely blank header cells, and keep subtotals out of the raw records. A column intended for summing should contain numbers consistently; a mixture of numeric values and text can lead to counts instead of the expected sum or to values being omitted.
The example detects a range at the moment the macro runs and creates its cache from that range. Adding records later does not automatically make that already-created fixed-size source range larger. For recurring reports, consider converting the source to an Excel Table with Insert > Table, then design the automation to use or rebuild from that table as its records grow. A table expands as rows are added, but a PivotTable still needs a refresh, and automation must ensure its cache points to the expanded table source.
Also confirm that the source and destination do not overlap. Keep report output on a separate worksheet or well away from the source list, and choose a destination where the resulting PivotTable has room to expand.
Refreshing existing PivotTables
After source values change, refreshing updates the PivotTable using its current cache source. To refresh one report in VBA, call its RefreshTable method. To refresh every PivotTable in the workbook, loop through worksheets and their PivotTables:
Sub RefreshWorkbookPivotTables()
' Refresh each PivotTable in this workbook.
Dim ws As Worksheet
Dim pt As PivotTable
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub
Excel’s Data > Refresh All command is another option when a workbook also contains external connections or other refreshable data. In VBA, ThisWorkbook.RefreshAll requests a refresh of workbook data connections and reports, but some external connections can refresh asynchronously. If later code depends on the refreshed results, account for the connection’s completion behavior rather than assuming every refresh has finished immediately.
A refresh updates records within the cache’s source; it is not a universal command to discover new rows beyond a fixed range. If new records do not appear, inspect the PivotTable’s source and cache before repeatedly refreshing.
Changing fields, filters, and calculations
Once a PivotTable exists, VBA can rearrange its fields by setting each field’s Orientation to a constant such as xlRowField, xlColumnField, or xlPageField. A field placed in the values area is usually added with AddDataField, as in the example. The chosen calculation must suit the data: summing sales is meaningful for numeric amounts, while counting records is useful when each source row represents an event.
For a basic report, a page field provides a worksheet filter that a reader can change. More elaborate automation can set item visibility or use a report filter, but filtering by item names requires those items to exist in the source and can be complicated by hidden items or changed data. Test filtering code against realistic source variations instead of assuming every category is always present.
When changing an existing report, check for layout conflicts and whether a field is already assigned elsewhere. A field cannot simply be treated as an independent copy in every area. It is often safer to clear or reset the relevant layout deliberately before applying a new arrangement, especially when a macro may be run more than once.
Common errors and practical fixes
- “Subscript out of range”: A worksheet name in the code may not exactly match a tab name, or the named sheet may not exist in
ThisWorkbook. - Field not found: The header text in VBA must match a source header. Check spelling, trailing spaces, blank headers, and whether the detected range includes the header row.
- PivotTable name already exists: PivotTable names must be unique in the workbook. Use a new name, or deliberately remove or replace the old report before creating another.
- Destination overlaps another report: Clear the destination area or choose another location. A PivotTable may need more cells after its fields or categories change.
- Numbers appear as counts: Check whether the source column contains text-formatted numbers, blanks, or mixed data types, then correct the source and refresh.
- New records are missing: Verify that the cache source includes them. Refreshing cannot extend a fixed source address by itself.
For production macros, add checks for missing sheets, empty source data, and existing report names before attempting to create the PivotTable. Error handling should report a useful problem to the user rather than silently hiding a failed operation.
Security, compatibility, and limitations
VBA runs in the desktop versions of Excel, subject to macro security settings. Do not enable macros in an unfamiliar workbook simply to view a PivotTable; macros can perform actions beyond report formatting. Use a trusted source, review code before enabling it, and keep an untouched backup before running code that modifies workbook contents.
The code here is written for Excel for Microsoft 365 on Windows. Excel for Mac also supports VBA and PivotTables, but the Visual Basic Editor access and keyboard shortcuts differ; on many Mac keyboards, Option+F11 opens the editor, though function-key settings can affect the shortcut. The VBA editor and available automation features are not available in Excel for the web, so a macro must be run in desktop Excel. A workbook containing VBA should be saved in a macro-enabled format such as .xlsm if the code needs to be retained.
VBA is useful for repeatable desktop workflows, but a macro that depends on exact sheet names, headers, or report locations can be brittle when a workbook changes. Document those assumptions and test with a copy of the actual workbook structure.
When to use VBA instead of other options
Use VBA when a user needs a repeatable sequence—such as creating a standard report, refreshing a group of PivotTables, or applying the same layout to many workbooks. For a one-time summary, the Excel interface is often faster: select the source and choose Insert > PivotTable. For a workbook that needs a modern refresh workflow or data transformation, Power Query can prepare and combine data before a PivotTable summarizes it. Power Query does not replace every PivotTable task, and VBA can still automate parts of a desktop workbook, but choosing the simplest maintainable tool usually reduces future repair work.
Another common misconception is that VBA and PivotTables are competing features. They are not: the PivotTable is the summary object, while VBA is one way to instruct Excel to create or manage it. You can create a report manually and use a small macro only to refresh it, rather than automating every layout decision.
Frequently Asked Questions
How do I refer to a PivotTable in VBA?
Use a worksheet’s PivotTables collection, for example Worksheets("Report").PivotTables("ptSales"). The worksheet and PivotTable names must exist in the workbook.
How do I refresh a PivotTable with VBA?
Call RefreshTable on the PivotTable object, such as pt.RefreshTable. This refreshes the report from its current source; it does not automatically enlarge a fixed source range.
Can VBA create a PivotTable from a changing data range?
Yes, but the code must identify the updated source each time or use a suitably designed Excel Table-based source. A macro that stores a fixed address will not include later rows outside that address just because it is refreshed.
Why does my PivotTable show Count instead of Sum?
Excel may interpret the field as nonnumeric if its source column contains text, mixed values, or other inconsistencies. Clean the source column, refresh, and verify the selected summary calculation in the PivotTable.
Can I run PivotTable VBA in Excel for the web?
No. Excel for the web can display and work with many PivotTables, but it cannot run VBA macros. Open the workbook in desktop Excel to run VBA.
Related
- Data Validation Studio: Excel Rules, Setup, and Limits
- Power Query Circular Reference: Causes and Fixes
- Excel Data Validation Source: Lists, Ranges, and Rules
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA UserForm Dynamic List: Populate and UpdateTo create a dynamic list in an Excel VBA UserForm, clear a ListBox or ComboBox and refill it…
- Excel VBA: Add Double Quotes to a String in VBA CodeTo add double quotes around a string in Excel VBA, concatenate a quote character before and after the…
- VBA MsgBox Scroll: Limits and Scrollable AlternativesNo: VBA’s built-in MsgBox has no setting or argument that adds a scrollbar. For long text, use a…
- Add Libraries in Excel: Add-ins, VBA References & MoreTo add a library in Excel, first decide whether you mean an Office add-in, an Excel add-in file,…
- VBA UserForm: How to Show It in ExcelTo show a VBA UserForm in Excel, call its name followed by .Show, such as UserForm1.Show. Add vbModeless…
- Excel VBA Copy and Paste Cells: Examples and OptionsTo copy a cell with Excel VBA, use the source range’s Copy method and specify a destination: sourceCell.Copy…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.