Yes—you can write VBA to create, refresh, and configure Excel PivotTables, including their row, column, and value fields. In Excel for Microsoft 365 on Windows, the usual approach is to use a PivotCache as the data source and a PivotTable object to control the report; the examples below show a complete starting point.
What VBA can do with a PivotTable
VBA is useful when a PivotTable routine needs to be repeated: for example, preparing a monthly report, refreshing several summaries after importing new rows, or producing the same layout for different departments. The PivotTable object model gives a macro access to a report’s fields, filters, layout, and refresh methods. A PivotCache stores the data connection or source used by one or more PivotTables.
A macro can perform tasks such as:
- Create a PivotTable from a worksheet range or an Excel Table.
- Place fields in rows, columns, filters, or the values area.
- Set a calculation such as sum or count, apply number formats, and refresh the report.
- Loop through PivotTables in a workbook and refresh them in one operation.
VBA does not remove the need for a well-structured source. PivotTable source data should have one header row, a distinct heading for every column, and consistent data types within each column. Blank headers, merged cells, and totals mixed into the source can cause errors or confusing results.
Prepare the workbook and source data
For a repeatable report, convert the source range to an Excel Table rather than relying on a fixed address such as A1:F500. Select a cell in the data and choose Insert > Table, confirm that the table has headers, then give it a recognizable name on the Table Design tab, such as SalesData. A table expands as new rows are added, which makes it a practical source for recurring reports. Confirm that the column headings used by the macro match the table headings exactly.
Before writing code, decide where the PivotTable should go and what its layout should be. In the example below, the workbook already has a worksheet named Data containing the SalesData table and a blank worksheet named Summary. The table has Region, Product, and Sales columns. The report will put Region in rows, Product in columns, and total Sales in the values area.
Create a PivotTable with VBA
In Excel for Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the macro into the standard module. The code assumes the sheet and table names above are present and that Summary!A3 is clear. It creates a new PivotTable; it is not designed to run repeatedly on top of an existing report with the same name.
Sub BuildSalesPivot()
Dim wb As Workbook
Dim wsData As Worksheet
Dim wsReport As Worksheet
Dim sourceTable As ListObject
Dim cache As PivotCache
Dim pt As PivotTable
Dim destination As Range
Set wb = ThisWorkbook
Set wsData = wb.Worksheets("Data")
Set wsReport = wb.Worksheets("Summary")
Set sourceTable = wsData.ListObjects("SalesData")
Set destination = wsReport.Range("A3")
' Build a cache from the named Excel Table.
Set cache = wb.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=sourceTable.Name)
' Create the PivotTable at the destination cell.
Set pt = cache.CreatePivotTable( _
TableDestination:=destination, _
TableName:="ptSalesSummary")
With pt
.PivotFields("Region").Orientation = xlRowField
.PivotFields("Region").Position = 1
.PivotFields("Product").Orientation = xlColumnField
.PivotFields("Product").Position = 1
.AddDataField .PivotFields("Sales"), "Total Sales", xlSum
.DataFields("Total Sales").NumberFormat = "$#,##0"
End With
End Sub
The field names in the code must match the source headers. The TableName argument also needs to be unique among PivotTables in the workbook, and the destination cannot overlap another PivotTable or occupied cells. If you run the macro again, remove or rename the earlier report first, or adapt the code to reuse an existing PivotTable. The sample deliberately does not delete a report automatically, because deleting or clearing worksheet content without checking what is there can destroy useful work.
After pasting the macro, place the cursor inside BuildSalesPivot and press F5 to run it from the editor. On many Mac keyboards the editor shortcut is Option+F11, sometimes Fn+Option+F11; the Developer tab’s Visual Basic button is another route. Menu labels and keyboard behavior can vary with Mac settings. Excel for the web does not run VBA macros.
Refresh existing PivotTables
Once source data changes, a PivotTable usually needs a refresh before the report reflects the new records or updated values. This macro visits PivotTables on each worksheet in the current workbook and refreshes them. It restores the previous screen-updating setting even if an error occurs.
Sub RefreshWorkbookPivotTables()
Dim ws As Worksheet
Dim pt As PivotTable
Dim previousScreenUpdating As Boolean
previousScreenUpdating = Application.ScreenUpdating
On Error GoTo Failed
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
Application.ScreenUpdating = previousScreenUpdating
MsgBox "PivotTables refreshed.", vbInformation
Exit Sub
Failed:
Application.ScreenUpdating = previousScreenUpdating
MsgBox "Refresh stopped: " & Err.Description, vbExclamation
End Sub
This refreshes PivotTables, but it is not a universal replacement for updating every external data connection. If the source comes from a database, Power Query, or another connection, the connection may need to refresh first; in that situation, check the workbook’s connection settings and choose an appropriate refresh sequence. A PivotTable based on the Excel Data Model also has model-specific considerations. For a simple worksheet table, add new records to the table and refresh the report.
How the object model fits together
Understanding four objects makes most PivotTable code easier to read. A Workbook contains Worksheets; each worksheet can contain PivotTables. A PivotCache represents the source data used for the report. The PivotTable is the visible summary, and its PivotFields are the source columns that you position or configure.
The creation example first identifies the table and workbook, then creates a cache, then creates the report from that cache. Only after the PivotTable exists can the code set its field orientations. The constants xlRowField and xlColumnField place fields in the corresponding layout areas, while adding a data field with xlSum asks Excel to summarize its numeric values by addition. Other calculations may be appropriate: counting records, for instance, is not the same as summing a numeric field.
When a field is added to the values area, Excel may assign a default caption such as Sum of Sales. Giving it the explicit caption Total Sales makes the example’s later number-format reference predictable. For larger automation projects, use clear variable names and separate the tasks of locating a source, building a report, and formatting it. That makes it easier to identify which step failed.
Choosing between VBA, the ribbon, and other tools
For a one-off PivotTable, the ribbon is often faster: select the source and choose Insert > PivotTable, then arrange fields in the PivotTable Fields pane. VBA is a better fit when the same sequence must run many times, when several reports need consistent layouts, or when a macro must refresh reports after another procedure imports data.
Power Query is primarily a tool for importing, cleaning, and reshaping data; it can prepare a reliable source for a PivotTable, but it is not the same thing as using VBA to set a PivotTable layout. Office Scripts can automate workbook tasks in supported Microsoft 365 environments, including some PivotTable operations, and may be worth considering when the workflow must run in the browser. They use a different programming model, so VBA code cannot simply be pasted into an Office Script.
For Mac users, VBA is available in desktop Excel, although editor access and some workbook features can differ by version. Excel for the web can display workbooks containing macros, but it cannot execute VBA. If browser-based use is essential, choose a supported browser automation option or keep the macro-running step in desktop Excel.
Common errors and practical fixes
Many PivotTable macro failures come from names or workbook state rather than complicated VBA. Check the exact worksheet, table, and field names first. A misspelled header or a table that was renamed will cause the object lookup to fail. Also check whether the destination cell is inside another PivotTable or whether a PivotTable with the requested name already exists.
- Subscript out of range: a worksheet or table name in the code may not exist in ThisWorkbook. Check spelling, spaces, and which workbook contains the macro.
- Field not found: verify the source header text and remove blank or duplicate headings. A field label is not necessarily the same as a caption shown in the finished report.
- Destination conflict: select a clear area on the report sheet and ensure it does not overlap an existing PivotTable.
- Old results remain: refresh the PivotTable after source changes. Also confirm that new rows were added to the intended source table or range.
- Unexpected totals: inspect the source values for text-formatted numbers, blanks, or mixed types, and verify whether sum or count is the intended calculation.
During development, run the macro on a copy of the workbook and step through it with F8 in the Visual Basic Editor. This lets you inspect which statement triggers an error. Avoid suppressing every error with On Error Resume Next: it can hide a missing field and leave a report that looks complete but is wrong.
Macro safety and workbook compatibility
Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, so a backup is the practical way to recover from an unwanted change. Save a workbook that contains VBA in a macro-enabled format such as .xlsm; saving it as a standard .xlsx file removes the VBA project.
Only enable macros in files from a source you trust. Excel’s security prompts exist because macros can make broad changes to files and workbook contents. Review what a macro does before running it, especially if it deletes sheets, clears ranges, or connects to external services. The examples here create or refresh PivotTables and show messages; they do not include automatic deletion of existing worksheets or data.
Frequently Asked Questions
Can VBA create a PivotTable from an Excel Table?
Yes. The example uses a ListObject, Excel’s VBA object for an Excel Table, as the source for a PivotCache and creates a PivotTable from that cache. Update the sample’s sheet, table, and field names to match your workbook.
How do I refresh all PivotTables in a workbook?
Loop through each worksheet’s PivotTables collection and call RefreshTable, as in the refresh macro above. If the source depends on an external connection or the Data Model, refresh that source appropriately as well.
Can a VBA macro add fields to rows or values?
Yes. Set a field’s Orientation to xlRowField or xlColumnField to place it in those areas. Use AddDataField to put a field in the values area and specify a calculation such as xlSum.
Will PivotTable VBA run in Excel for the web?
No. Excel for the web can open a macro-enabled workbook, but it cannot run its VBA. Use desktop Excel to run VBA, or investigate Office Scripts for supported browser-based automation.
Why does the macro say a PivotTable field does not exist?
The code usually refers to a source column heading that Excel cannot find. Check the exact header, including spaces, and make sure the source range or table includes that column.
Can I undo a PivotTable macro with Ctrl+Z?
No. Excel clears its normal undo history when VBA changes workbook content. Save a separate copy before running a macro, particularly while developing or adapting code.
Related
- Power Query Remove Leading Zeros Without Losing IDs
- Power Query Reference: What It Does and When to Use It
- Excel Data Validation: Can You Select Multiple Items?
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Sort Table by Column: Code and Common FixesTo sort an Excel table by one column with VBA, get the table and column by name, clear…
- VBA MsgBox Constants: Buttons, Icons, and Return Values ExplainedVBA MsgBox constants control which buttons and icon appear in a message box, which button is selected by…
- Excel VBA Date to Number: Convert Dates to SerialsIn Excel VBA, convert a Date value to a number with CDbl(dateValue). The result is a serial value:…
- VBA Error Handling in Loops: Safe Patterns That WorkIn VBA, use an error handler to decide what a loop should do when one item fails: log…
- VBA MsgBox Multiple Lines: Line Breaks and ExamplesTo show multiple lines in a VBA MsgBox, join the text with the built-in line-break constant vbCrLf. For…
- Excel VBA: Protect a Sheet and Allow Filter and SortYes. In Excel VBA, protect the worksheet with AllowFiltering:=True and AllowSorting:=True. The filter controls must already exist, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.