Yes. VBA can create an Excel PivotTable by building a PivotCache from a source range and adding a PivotTable to a worksheet. In Excel for Microsoft 365 on Windows, the macro below groups sales by region and product; it also explains the setup, refresh behavior, and common errors to check.
What VBA does when it creates a PivotTable
A PivotTable is an interactive summary of tabular data. Instead of writing separate formulas for every category, you arrange source fields into areas such as Rows, Columns, Values, and Filters. VBA can automate that arrangement, which is useful when the same report must be produced regularly or when a workbook is generated from a repeatable process.
Creating one with VBA generally involves three objects. A PivotCache holds the source data used by the report, a PivotTable displays the summary, and PivotFields determine how columns are summarized. A cache can be reused for multiple PivotTables, although each report still has its own layout.
The example below uses a worksheet named Data with column headings in row 1, including Region, Product, and Sales. It places the PivotTable on an existing, empty worksheet named PivotReport. The source data must be one contiguous rectangular range, with a distinct heading in every column.
Example VBA macro for a sales PivotTable
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the code into the module, and adjust the worksheet names or field headings if your workbook uses different ones. Save a copy of the workbook before running a macro: VBA actions cannot be undone with Ctrl+Z.
Option Explicit
Sub CreateSalesPivot()
Dim wb As Workbook
Dim wsData As Worksheet
Dim wsReport As Worksheet
Dim lastRow As Long
Dim lastCol As Long
Dim sourceRange As Range
Dim cache As PivotCache
Dim report As PivotTable
Set wb = ThisWorkbook
Set wsData = wb.Worksheets("Data")
Set wsReport = wb.Worksheets("PivotReport")
' Find the source boundaries using column A and row 1.
lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
lastCol = wsData.Cells(1, wsData.Columns.Count).End(xlToLeft).Column
If lastRow < 2 Or lastCol < 1 Then
Err.Raise vbObjectError + 1000, , "The source needs headings and at least one data row."
End If
Set sourceRange = wsData.Range(wsData.Cells(1, 1), _
wsData.Cells(lastRow, lastCol))
' Create a cache from the current source range.
Set cache = wb.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=sourceRange.Address(ReferenceStyle:=xlR1C1, External:=True))
' PivotReport must be empty and must not already contain this PivotTable.
Set report = cache.CreatePivotTable( _
TableDestination:=wsReport.Range("A3"), _
TableName:="SalesPivot")
With report
.PivotFields("Region").Orientation = xlRowField
.PivotFields("Product").Orientation = xlColumnField
.AddDataField .PivotFields("Sales"), "Total Sales", xlSum
.RowAxisLayout xlTabularRow
End With
End Sub
The macro finds the last populated row by looking down column A and the last heading by looking across row 1. That is suitable for a clean dataset where column A is filled for every record. If the first column can contain blanks, use a dependable key column for the last-row calculation or turn the source into an Excel Table and base the range on that table. The final heading names in the code must match the source headings exactly.
This is intended as a first run on a blank report sheet. If you run it again without removing the old PivotTable or using a fresh output sheet, Excel may report that the destination overlaps an existing report or that the PivotTable name is already in use. The code does not delete existing report content, which avoids silently removing other information.
Preparing source data and the workbook
PivotTables work best when each row represents one record and each column represents one field. For example, a sales list might have one row per transaction and columns for date, region, product, and sales amount. Avoid merged headings, blank heading cells, completely empty rows inside the list, and totals mixed into the transaction records.
Before running the macro, check that:
- The source worksheet is named
Data, and the output worksheet is namedPivotReport. - Row 1 contains unique, nonblank headings, including the exact fields
Region,Product, andSales. - Column A has a value in every source record, because the example uses it to locate the last row.
- The report sheet is empty and has no existing PivotTable called
SalesPivot. - Sales values are stored as numbers if you want Excel to sum them as amounts.
For a workbook you will update often, converting the source list to an Excel Table with Insert > Table can make the data easier to maintain. A table expands as records are added. The sample macro, however, deliberately calculates a range from the current worksheet contents; it does not refer to a named Excel Table. You can adapt the source-range assignment if you want the macro to use a table by name.
Changing the layout and calculation
The example puts Region in the row area, Product in the column area, and Sales in Values. Those choices are independent: a date field could be a report filter, for example, or Product could be a row field. To change the layout, change the field names and their orientation settings. Excel provides constants such as xlRowField, xlColumnField, and xlPageField for those areas.
The AddDataField line adds Sales as a value and requests a sum. If the source values are numeric, the result is a total by Region and Product. If Excel treats the field as text or finds nonnumeric entries, it may summarize the field by count instead, or the result may not match the intended calculation. Check the PivotTable’s value field settings and clean the source values if that happens.
To count records instead of summing sales, change the calculation argument from xlSum to xlCount. A count is useful for questions such as how many transactions occurred in each region. Other supported summary choices include average, maximum, and minimum; select the constant that matches the report’s purpose rather than assuming every numeric field should be summed.
Refreshes, changing data, and PivotCaches
A PivotTable summarizes the data available through its cache; it does not automatically rewrite its source range every time the source sheet changes. Refreshing updates the report from the cache’s source, but it cannot include records outside a fixed source range. Because this example calculates the range when the macro runs, running the macro again with an updated source can build a cache that includes the new last row, provided you use a clean destination.
If you only need to refresh a PivotTable whose source range is already correct, VBA can call its RefreshTable method. For example, with the PivotTable already available as an object variable named report, report.RefreshTable refreshes it. You can also refresh from Excel’s PivotTable Analyze > Refresh command. If the data list grew beyond the original source, update the source or rebuild the cache; a refresh alone does not necessarily expand it.
When a workbook contains several reports based on the same data, sharing a cache can reduce duplicated cache storage and help keep reports aligned. It also means those reports may share some cache behavior. For a single report, creating one cache as in the example is straightforward. Large datasets, external connections, and the Data Model can require different design choices from this simple worksheet-range example.
Common errors and practical fixes
Many PivotTable macro errors are caused by a mismatch between the workbook and the assumptions in the code. Check the specific failure before changing the macro:
- Subscript out of range: Excel cannot find a worksheet with the exact tab name used in the code. Rename the sheet or update the string.
- Field name is not valid: A heading may be blank, duplicated, misspelled, or different from the field name in the macro. Check row 1 and remove merged heading cells.
- Destination overlaps a PivotTable: The output area is not clear, or the prior report remains on the sheet. Use a fresh blank report sheet or remove the old PivotTable before creating another.
- Unexpected count instead of sum: Inspect the Sales column for text-formatted numbers, blanks, or mixed values, then set the value calculation to the intended summary.
- Missing new records: Confirm that the source range includes them. The macro uses column A to find the last row, so gaps in that column can make its boundary too short.
- Macro does not run: Save the workbook as an Excel Macro-Enabled Workbook (
.xlsm) and use Excel’s macro security settings appropriately. Do not enable macros in a file from an untrusted source.
For troubleshooting, it can help to run the procedure one step at a time in the Visual Basic Editor with F8, checking the highlighted line and the worksheet names. This is a Windows shortcut; on Mac, Excel’s Visual Basic Editor and keyboard shortcuts can differ. VBA macros do not run in Excel for the web, so open the workbook in the desktop Excel application to run this code. Excel for Mac supports VBA, although interface paths and some capabilities can differ from Windows.
VBA versus creating a PivotTable manually
For a one-off report, the PivotTable interface is often quicker: select the source data, choose Insert > PivotTable, choose a destination, and arrange fields in the PivotTable Fields pane. That workflow is also helpful for exploring unfamiliar data because you can see the layout as you build it.
VBA is more valuable when the setup is repeated, must follow a consistent layout, or is part of a larger workbook process. A macro can create several reports, apply standard field placements, and be combined with other workbook automation. The trade-off is that code depends on expected sheet names, headings, and data structure. A small change to a heading can break a macro that worked previously, so document assumptions and validate the incoming data.
VBA is not the only automation option. Power Query is useful for importing and transforming recurring data, while Office Scripts can automate certain Excel tasks in supported Microsoft 365 environments. Those tools have different capabilities and execution environments; they are not drop-in replacements for every VBA procedure. For this particular task, VBA provides direct control over PivotCaches and PivotTables in desktop Excel.
Saving and running the macro safely
Store the code in a macro-enabled workbook if the macro needs to remain available in that file. A standard .xlsx workbook cannot retain VBA code when saved in that format. Save a backup first, especially if a macro will modify an existing report or process important source data.
The sample creates a PivotTable but does not delete source rows or overwrite cells in the Data sheet. It does create a report beginning at A3 on PivotReport, so keep that destination clear. Macro security is also important: only enable code you understand and trust. If you distribute a workbook, explain what the macro changes and what worksheet names and headings it expects.
Frequently Asked Questions
Can VBA create a PivotTable automatically?
Yes. VBA can create a PivotCache, add a PivotTable, and assign fields to its row, column, filter, and values areas. The macro above creates a sales summary from a worksheet range.
Can VBA refresh an existing PivotTable?
Yes. An existing PivotTable object can be refreshed with its RefreshTable method. The source range still needs to include the records you expect the refresh to summarize.
Can VBA create a PivotTable from multiple worksheets?
It is possible to automate reports involving multiple sources, but the simple range-based example here expects one contiguous source range on one worksheet. Combining tables or using the Data Model involves additional setup and is not the same as passing this one range.
Why does my PivotTable show Count instead of Sum?
Excel may be treating the values as text or encountering nonnumeric entries. Check and clean the source column, then choose Sum in the PivotTable value field settings if that calculation is appropriate.
Can I run VBA PivotTable code in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac; the exact interface and some VBA features can differ by platform.
Related
- Data Validation and Verification in Excel Explained
- Power Query Replace Text: Steps, Options, and Examples
- Conditional Formatting: Use More Than One Rule in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Loop Types: For, Do, and While ExplainedVBA has four commonly encountered loop constructs: For…Next, For Each…Next, Do…Loop, and While…Wend. Use For…Next when a counter…
- VBA For Loop Negative: How Step -1 WorksYes. A VBA For loop can count downward by using a negative Step, such as For i =…
- VBA AutoFilter by Color: Filter Fill and Font ColorsYes. In Excel VBA, use the AutoFilter method with Operator:=xlFilterCellColor to filter by a cell’s fill color, or…
- VBA Modify Pivot Tables in Excel: Examples and TipsYes. VBA can modify an Excel PivotTable by moving fields between rows, columns, filters, and values; setting report…
- VBA GoTo: Syntax, Examples, and Safer AlternativesIn VBA, GoTo is a statement, not a function: it transfers execution to a labeled line in the…
- Excel VBA Sort Method of Range Class Failed: Fixes“Sort method of Range class failed” is usually Excel’s run-time error 1004: a VBA sort request could not…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.