To create a PivotTable with VBA, select a worksheet containing a header row and data, then run a macro that builds a PivotCache, creates a PivotTable, and assigns fields to rows, columns, and values. The example below summarizes Sales by Region and Product; change its field names to match your own column headings.
What the macro does
Excel’s PivotTable interface lets you summarize data by dragging fields into areas such as Rows, Columns, Values, and Filters. VBA automates those same building blocks. A PivotCache represents the source data, and a PivotTable displays a summary based on that cache. The macro creates both objects, places the report on a new worksheet, and configures the summary fields.
The example assumes the source worksheet has headers in row 1, records beginning in row 2, and data in columns A onward. Column A should contain a value for each record so the macro can determine the last used row. It expects columns named exactly Region, Product, and Sales. For example, a source table might have Date, Region, Product, Sales, and Units columns. The report will show regions down the rows, products across the columns, and the sum of Sales in the body.
Before running any macro, save a copy of the workbook. VBA actions generally cannot be reversed with Ctrl+Z, so a backup is a useful safeguard, particularly when adapting code that changes or deletes existing sheets.
VBA code for a basic PivotTable
Click a cell in the source data before running this procedure. It finds the bottom row from column A and the rightmost header from row 1, then creates a new report sheet. It does not delete or overwrite existing sheets, so each run creates another report.
Sub CreateSalesPivot()
' Build a PivotTable from the active worksheet.
Dim wb As Workbook
Dim sourceWs As Worksheet
Dim reportWs As Worksheet
Dim sourceRange As Range
Dim headerCell As Range
Dim cache As PivotCache
Dim pt As PivotTable
Dim lastRow As Long
Dim lastCol As Long
Set wb = ActiveWorkbook
Set sourceWs = ActiveSheet
' Find the source boundaries using column A and row 1.
lastRow = sourceWs.Cells(sourceWs.Rows.Count, 1).End(xlUp).Row
lastCol = sourceWs.Cells(1, sourceWs.Columns.Count).End(xlToLeft).Column
If lastRow < 2 Or lastCol < 1 Then
MsgBox "Add a header row and at least one data row first."
Exit Sub
End If
Set sourceRange = sourceWs.Range(sourceWs.Cells(1, 1), _
sourceWs.Cells(lastRow, lastCol))
' PivotTable source fields need headings.
For Each headerCell In sourceRange.Rows(1).Cells
If Len(Trim$(CStr(headerCell.Value))) = 0 Then
MsgBox "Every source column must have a heading."
Exit Sub
End If
Next headerCell
Set reportWs = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
reportWs.Range("A1").Value = "Sales summary"
' Use a workbook- and worksheet-qualified source address.
Set cache = wb.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=sourceRange.Address( _
ReferenceStyle:=xlR1C1, External:=True))
Set pt = cache.CreatePivotTable( _
TableDestination:=reportWs.Range("A3"))
With pt.PivotFields("Region")
.Orientation = xlRowField
.Position = 1
End With
With pt.PivotFields("Product")
.Orientation = xlColumnField
.Position = 1
End With
pt.AddDataField pt.PivotFields("Sales"), "Total Sales", xlSum
reportWs.Columns.AutoFit
MsgBox "PivotTable created on " & reportWs.Name & "."
End Sub
The code is deliberately explicit: the variable names identify the workbook, source worksheet, report worksheet, source range, cache, and finished PivotTable. The header check catches blank headings, but it does not verify that the three requested field names exist. If your headings differ, replace the text inside PivotFields with the exact heading text in your source. If a field is missing, Excel will report an error when the macro tries to use it.
How to add and run the macro
- Open the workbook in desktop Excel for Microsoft 365 on Windows, and select the worksheet containing the source data.
- Open the Visual Basic Editor with
Alt+F11, or use Developer > Visual Basic. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. - In the editor, choose Insert > Module, then paste the complete procedure into the module window.
- Save the workbook as an Excel Macro-Enabled Workbook (
.xlsm) if it needs to retain the VBA code. - Return to Excel and run it using
Alt+F8, selectCreateSalesPivot, and choose Run. You can also run the procedure from the Visual Basic Editor.
On Excel for Mac, the Visual Basic Editor is available from Developer > Visual Basic; keyboard shortcuts can depend on the keyboard, and Option+F11 (sometimes with Fn) is the common equivalent of Windows Alt+F11. The macro dialog is commonly opened with Option+F8. Excel for the web can open a workbook that contains VBA, but it cannot create or run VBA macros; use the desktop application.
Change the fields and summary
The field orientation controls where a field appears in the report. xlRowField places Region down the left, while xlColumnField puts Product headings across the top. The AddDataField line puts Sales in the Values area and requests a sum. PivotTables may summarize numeric fields by sum and nonnumeric fields by count; specifying xlSum makes the intended calculation clear, but the Sales source values still need to be usable as numbers.
To adapt the layout, edit the field names and orientation assignments. For instance, if the source has Department, Month, and Hours columns, Department could be the row field, Month the column field, and Hours the value field. Replace the corresponding names in the code, and use an aggregation that suits the data. Common built-in choices include xlSum, xlCount, and xlAverage. A count is useful for counting records; an average is appropriate only when the average of the underlying values answers the question you have.
You can add a report filter by assigning another field to xlPageField. For example, a field called Year could be added with pt.PivotFields("Year").Orientation = xlPageField. The field name must match a source header. For more complex reports, set field orientations and positions deliberately, and consider whether a field belongs in the report at all; a PivotTable is most useful when its layout makes the comparison easy to interpret.
Prepare source data for reliable results
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. Avoid merged header cells, completely blank rows inside the list, subtotals mixed into the records, and blank column headings. A column should also use a consistent data type. For example, if some Sales entries are stored as text such as “1,200” while others are numbers, the Values area may not calculate the intended sum.
The sample determines the last row by looking up from the bottom of column A. If column A has gaps, or if the records continue below the last nonblank cell in that column, the detected range may be incomplete. Choose a reliably populated key column for that calculation, or revise the range-detection logic for your layout. The last column is determined from row 1, so headings need to be on that row and extend across every field you want included.
For a source list that grows regularly, converting it to an Excel Table with Insert > Table can make the source easier to maintain. A Table expands as new rows are added, whereas the sample macro captures a cell range at the time it runs. To use a Table as the PivotCache source, adapt the cache setup to refer to the Table by name, and confirm the reference syntax in the workbook. This is especially helpful when a fixed range would otherwise omit new records.
Refresh data and run the macro again
A PivotTable is based on a cache, so changing source cells does not necessarily update the displayed summary immediately. For an existing report, click inside its PivotTable and choose PivotTable Analyze > Refresh, or use Data > Refresh All to update workbook connections and PivotTables. In VBA, an existing PivotTable can be refreshed with its RefreshTable method.
Refreshing and expanding the source are separate tasks. If records are added beyond the range captured when the macro built the cache, a refresh may still use the old range and leave new rows out. Update the PivotTable’s data source or build it from a growing Excel Table, then refresh. Running the sample again recalculates its boundaries and creates a fresh PivotTable on another worksheet, but leaves older report sheets in place. Remove unneeded reports manually after checking them; the macro intentionally avoids deleting user content.
Common errors and practical checks
- “Field name is not valid” or a missing field error: Check that the source has a complete header row and that the names in the code match the headers exactly. Extra spaces, spelling differences, and a heading outside the detected range can all cause a mismatch.
- The report omits the newest records: Check the detected source boundaries, especially whether column A is populated throughout. If the report already exists, also check its data source before refreshing.
- Sales is counted instead of summed, or sums are wrong: Look for text values, blanks, or inconsistent entries in the Sales column. Convert values to numeric data where appropriate, then refresh or recreate the report.
- The macro does not run: Confirm the workbook is open in desktop Excel, saved in a macro-enabled format when retaining code, and that your organization’s macro security settings permit the file. Do not enable macros in an untrusted workbook just to make a procedure run.
- Repeated runs leave many sheets: This example creates a new worksheet every time. Rename, review, and remove old reports yourself, or add carefully designed sheet-reuse logic if the report should be replaced automatically.
Macro security is worth treating as part of the workflow, not as an obstacle to click past. Excel may block macros from files considered untrusted, and workplace policy may restrict them. Use code you understand, keep a backup, and check the workbook’s source before enabling its macros. These precautions matter because VBA can change workbook contents and those changes are not undone by the normal Undo command.
When a manual PivotTable or another tool is better
For a one-time summary, creating a PivotTable through Insert > PivotTable is often quicker than writing VBA. The dialog lets you confirm the source and choose where the report goes, and the PivotTable Fields pane provides an interactive way to arrange the layout. VBA is more useful when the same report must be recreated repeatedly, when a larger process needs to prepare and format reports, or when a user-facing button should produce a standard layout.
Power Query is another option when the main challenge is importing, cleaning, combining, or reshaping data before summarizing it. It is not a direct replacement for every PivotTable layout, but it can make repeatable data preparation easier to manage. A common workflow is to use Power Query to prepare a clean table and then a PivotTable to explore or present totals. Choose based on the task: VBA automates Excel actions, a PivotTable summarizes fields interactively, and Power Query focuses on transforming data.
Frequently Asked Questions
Can VBA create a PivotTable automatically?
Yes. VBA can create a PivotCache and PivotTable, set row, column, filter, and value fields, and apply formatting. The example creates a basic report from the active worksheet and places it on a new sheet.
Why does my macro say a PivotTable field name is not valid?
Check that every source column has a heading and that each field name in the code matches a heading in the detected source range. Also verify that the source range includes the header row and all the columns you need.
Can I create a PivotTable from an Excel Table?
Yes. An Excel Table can be used as a PivotTable source, and it expands as rows are added. The sample uses a worksheet cell range instead, so its cache-source code must be adapted if you want to reference a named Table.
Does refreshing a PivotTable include newly appended rows?
Only if those rows fall within the PivotTable’s current source. Refresh updates the cache from its configured source; it does not necessarily enlarge a fixed source range. Expand the source or use a growing Excel Table, then refresh.
Can I run this VBA macro in Excel for the web?
No. Excel for the web can retain and open workbooks containing VBA, but VBA macros run in the desktop Excel application. Open the workbook in Excel for Windows or Mac to edit or run the procedure.
Related
- Excel Data Validation Source: Lists, Ranges, and Rules
- Excel Data Validation Rules: How They Work and When to Use Them
- Excel Data Validation Restrictions and Their Limits
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Object Required? Causes and FixesIn Excel VBA, “Object required” is usually run-time error 424: VBA expected an object reference but received a…
- Excel VBA Exit Sub: How It Works and When to Use ItIn Excel VBA, Exit Sub immediately ends the currently running Sub procedure and returns control to the code…
- Excel VBA Day From Date: Weekday Numbers and NamesIn Excel VBA, use Day(dateValue) to return the day of the month, such as 14, and Weekday(dateValue, vbMonday)…
- Excel VBA Data Types: Choosing the Right Variable for MacrosExcel VBA data types define what kind of value a variable can hold, such as a whole number,…
- Excel VBA Find Cell: Code, Options, and ExamplesIn Excel VBA, use the Range.Find method to locate a cell containing specified text or a value. Set…
- Running Macros in Google Sheets: How to Run and Manage ThemTo run a macro in Google Sheets, open the spreadsheet in a web browser, choose Extensions > Macros,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.