Yes. In Excel for Microsoft 365 on Windows, VBA can create a PivotTable directly from an Excel table (a ListObject) by building a PivotCache from the table and placing a PivotTable on a worksheet. The example below summarizes a table named SalesData by region and product; change the table and field names to match your data.
What the macro needs
Before using the code, make sure the source data is formatted as an Excel table, not merely a range with headings. Click a cell in the data and choose Insert > Table, or press Ctrl+T on Windows (Command+T on Mac). Confirm that the table has headers. In Table Design, check the Table Name box and, if needed, give it a simple name such as SalesData.
The example expects the table to be on a worksheet named Data and to have three columns with these exact headings: Region, Product, and Sales. The macro puts each region on a row, each product in a column, and sums Sales. Excel matches field names exactly, so a heading such as “Sales Amount” requires that same wording in the VBA.
- Keep one record per row and one variable per column; avoid merged cells and blank header cells.
- Use a numeric value in every Sales cell you expect to sum. Text-formatted amounts may be counted rather than summed.
- Save the workbook as an Excel Macro-Enabled Workbook (
.xlsm) so the VBA code can be retained.
VBA macro to create the PivotTable
Open the Visual Basic Editor, choose Insert > Module, and paste the complete code below into the module. It creates a new report sheet each time it runs, so it does not erase an existing report. The helper function checks that the generated sheet name is not already in use.
Option Explicit
Sub CreateSalesPivot()
Const SOURCE_SHEET As String = "Data"
Const SOURCE_TABLE As String = "SalesData"
Dim wb As Workbook
Dim wsSource As Worksheet
Dim wsReport As Worksheet
Dim lo As ListObject
Dim pc As PivotCache
Dim pt As PivotTable
Dim reportName As String
Dim pivotName As String
Dim suffix As Long
Set wb = ThisWorkbook
Set wsSource = wb.Worksheets(SOURCE_SHEET)
Set lo = wsSource.ListObjects(SOURCE_TABLE)
If lo.DataBodyRange Is Nothing Then
MsgBox "The source table has headers but no data rows.", vbExclamation
Exit Sub
End If
' Find an unused worksheet name for this report.
reportName = "Pivot_" & Format(Now, "yyyymmdd_hhnnss")
suffix = 1
Do While WorksheetExists(reportName, wb)
reportName = "Pivot_" & Format(Now, "yyyymmdd_hhnnss") & "_" & CStr(suffix)
suffix = suffix + 1
Loop
Set wsReport = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
wsReport.Name = reportName
' Use the Excel table name as the PivotCache source.
Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=lo.Name)
pivotName = "pt_" & Replace(reportName, "Pivot_", "")
Set pt = pc.CreatePivotTable( _
TableDestination:=wsReport.Range("A3"), _
TableName:=pivotName)
With pt
.PivotFields("Region").Orientation = xlRowField
.PivotFields("Region").Position = 1
.PivotFields("Product").Orientation = xlColumnField
.PivotFields("Product").Position = 1
.AddDataField .PivotFields("Sales"), "Total Sales", xlSum
End With
wsReport.Range("A1").Value = "Sales by region and product"
wsReport.Columns.AutoFit
MsgBox "PivotTable created on " & reportName, vbInformation
End Sub
Private Function WorksheetExists(ByVal sheetName As String, _
ByVal wb As Workbook) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = wb.Worksheets(sheetName)
On Error GoTo 0
WorksheetExists = Not ws Is Nothing
End Function
The helper is part of the same module and should be pasted along with the main procedure. The macro uses the workbook containing the code (ThisWorkbook), which is usually what you want when the macro is stored in the data workbook. It does not change the source table or delete existing worksheets.
Run the macro and check the result
On Windows, open the Visual Basic Editor with Alt+F11, then use Insert > Module to add a standard module. Return to Excel and run CreateSalesPivot from Developer > Macros, or press Alt+F8, select the macro, and choose Run. If the Developer tab is hidden, turn it on under File > Options > Customize Ribbon.
On a Mac, VBA is available in the desktop version of Excel, though shortcut behavior can depend on the keyboard and function-key settings; Option+F11 or Fn+Option+F11 may open the Visual Basic Editor, and Developer > Macros is another way to run the code. Excel for the web does not run VBA macros. Use desktop Excel to create or run this macro, and check your organization’s macro security settings if Excel blocks it.
After the macro runs, look for a new worksheet named with a Pivot_ prefix. The PivotTable begins at cell A3. Confirm that the row labels, column labels, and totals make sense before relying on the summary. Because each run creates a new sheet, remove old report sheets manually if they are no longer needed.
How the table, cache, and PivotTable fit together
The source table stores the records; the PivotCache is the PivotTable’s cached view of those records; and the PivotTable arranges and aggregates fields from that cache. VBA first finds the ListObject by its worksheet and table names, then creates a cache using the table name as its source. It then creates a PivotTable at a destination cell and assigns fields to layout areas.
Using a table rather than a fixed address is useful because tables expand when records are added beneath them. The table name remains stable even when the number of rows changes, so the macro does not need a hard-coded range such as A1:C500. However, adding records does not automatically guarantee that an already-created PivotTable shows them: the PivotTable may still need its cache refreshed.
The source table must be in the same workbook for this specific code path. If the source is in another workbook, an external data connection, or a database, the cache setup and connection details differ. The example is intentionally for a local Excel table.
Customize fields and the summary
PivotTable field names in the code must correspond to column headings in the table. A heading containing spaces is fine when written exactly inside the quoted VBA string. To summarize a different measure, replace Sales with the relevant heading and change the caption Total Sales to a clear label. The third argument xlSum requests a sum; other common choices include xlCount and xlAverage.
To put a field in a different area, set its Orientation to xlRowField, xlColumnField, xlPageField (a report filter), or xlDataField. For example, a date field can be placed in the row area, while a category field can be used as a filter. A data field’s aggregation depends on its values and the function requested. If Excel treats numeric-looking values as text, a sum may not behave as expected; correct the source values first.
For a different report layout, the PivotTable can also be formatted or sorted after creation. Keep layout changes separate from source cleanup: PivotTables summarize the data they receive, but do not repair inconsistent labels such as “North”, “north”, and “NORTH”. Standardizing those entries in the source table can prevent confusing duplicate categories.
Refresh after the source table changes
When new records are added to the Excel table, refresh the relevant PivotTable to update its results. Select a PivotTable and choose PivotTable Analyze > Refresh, or use Data > Refresh All to refresh workbook connections and PivotTables. A VBA routine can also call the PivotTable’s RefreshTable method if you want an existing report refreshed rather than creating another one.
For repeated automation, consider changing the example so it finds a designated report sheet and refreshes its existing PivotTable. That approach avoids accumulating one new worksheet per run, but requires careful handling of an existing PivotTable and its layout. The sample deliberately favors a new output sheet because it leaves earlier reports untouched. Remember that a saved PivotTable may display cached results until refreshed; refreshing is especially important after changing source records.
Common errors and how to fix them
- Subscript out of range: Excel could not find the worksheet named
Data. ChangeSOURCE_SHEETto the worksheet tab name, including spaces and punctuation. - Unable to get the ListObjects property: The named table is not on that worksheet or its name differs. Check Table Design > Table Name, rather than relying on the table’s displayed heading.
- Field name not found: A heading in the macro does not exactly match a table column, or a header is blank or duplicated. Correct the heading or update the quoted field name.
- Cannot group or sum the field: The measure may contain text, errors, or inconsistent values. Clean the source column and verify that numeric entries are genuine numbers.
- Macro is blocked or does not run: The file may be saved as
.xlsx, opened in Excel for the web, or restricted by security policy. Save a working copy as.xlsmand follow your organization’s approved macro settings.
If the macro stops after it has created a report sheet, inspect that sheet before running again. A partially created sheet may remain if a field name or data type causes an error. The code does not include a broad error handler that hides such problems, which makes the underlying issue easier to identify.
When VBA is useful, and alternatives
VBA is a good fit when a repeatable report must be created with the same field arrangement, when a workbook needs a one-click process, or when a larger macro already performs other steps. It is less suitable when users cannot enable macros, when the report is a one-time task, or when the workbook needs to work in a browser without desktop Excel.
For a one-off PivotTable, select a cell in the table and choose Insert > PivotTable; Excel will use the table as the source, and you can choose a new or existing worksheet. For data preparation that must be repeated, Power Query can import and transform records before loading them for analysis. These alternatives can reduce VBA maintenance, but they do not use this macro’s exact automation flow. In all cases, retain a copy of important source data and verify the report fields and totals.
Frequently Asked Questions
Can VBA create a PivotTable from an Excel table by name?
Yes. Find the table as a ListObject and create a PivotCache using its table name as the source. The example uses SalesData, so replace that constant with the table’s actual name.
Does the PivotTable update automatically when I add rows?
The table can expand to include new rows, but an existing PivotTable generally needs a refresh to show changed source data. Use PivotTable Analyze > Refresh or Data > Refresh All, or add a refresh step to your VBA process.
Can I use this macro with any table headings?
Yes, but the field references in the code must match the table’s headings exactly. Change Region, Product, and Sales to the headings in your own table and confirm that the selected data field contains values appropriate for the chosen summary.
Will the macro work in Excel for the web?
No. Excel for the web does not run VBA. Use the desktop Excel application on Windows or Mac to run the macro; the workbook can still be opened in a browser for other supported tasks.
Can I undo a macro with Ctrl+Z?
No. Excel’s Undo history does not reverse VBA actions. Save a copy of the workbook before running a macro, particularly before adapting the code to clear, replace, or modify existing sheets or PivotTables.
Related
- How to Paste an Image in Excel: Steps, Tips, and Fixes
- How to Paste Into Filtered Cells in Excel
- How to Paste Filtered Data in Excel Without Errors
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Open a File Dialog to Select a FolderTo let someone choose a folder in Excel VBA, use Application.FileDialog(msoFileDialogFolderPicker). Show the dialog, check whether the user…
- Excel VBA Block Comments: How to Comment Out Code SafelyExcel VBA has no special multi-line block-comment syntax such as /* … */. To comment out several lines,…
- Excel VBA: Find Unique Values in a ColumnTo find unique values in an Excel column with VBA, loop through the source cells, keep the first…
- VBA Range in Excel: How to Reference CellsIn Excel VBA, a Range is an object that represents one cell or a group of cells. To…
- TinyTask Macro: Is It Free and How Does It Work?Yes. TinyTask is a free, lightweight Windows utility that records mouse and keyboard actions and plays them back…
- Excel Macro Extension: Which File Type Should You Use?The standard Excel macro-enabled workbook extension is .xlsm. Use it when saving a workbook that contains VBA macros;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.