How to Create a Pivot Table Using VBA in Excel 365

To create a PivotTable with VBA in Excel for Microsoft 365 on Windows, put your source data on a worksheet named Data, open the Visual Basic Editor, insert a module, and run a macro that creates a PivotTable on a report sheet. The example below summarizes sales by region and category; change the field names to match your own headers. Save a copy of your workbook first, because macro changes cannot be undone with Ctrl+Z.

What You’ll Need

Excel for Microsoft 365 desktop on Windows. Excel for the web can open a macro-enabled workbook but cannot create or run VBA macros.
A workbook with a worksheet named Data. Put one header row above the data, with no blank header cells, and keep the data in a continuous rectangular block.
Fields named Region, Category, and Sales for the sample macro. The sales values should be numeric. You can substitute your own exact column headings in the code.
A macro-enabled workbook format, .xlsm, if you want to retain the macro after closing Excel. Only enable macros in files you trust.

Step-by-Step

Prepare and check the source data. On the Data sheet, place the field names in row 1 and records beneath them. For example, columns A, B, and C could be Region, Category, and Sales. Remove completely blank rows or columns within the data, merge no cells in the source area, and make sure each column has one unique, nonblank heading. The macro uses the contiguous block around A1, so avoid unrelated content directly beside or below that block.
Save a working copy as a macro-enabled file. Use File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). Keep a separate copy before running the macro. A PivotTable is created by the code, and Ctrl+Z cannot reverse a macro’s changes.
Open the Visual Basic Editor and add a module. In Windows Excel, press Alt+F11, then choose Insert > Module in the editor. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer. On a Mac, use Option+F11 (some keyboards require Fn+Option+F11), or choose Developer > Visual Basic. If needed, show the Developer tab using Excel > Settings > Ribbon & Toolbar. Excel for the web does not provide the VBA editor.
Paste this macro into the module. It reads the current data block on Data, removes and recreates a sheet named PivotReport, then builds a PivotTable starting at A3. It places Region in rows, Category in columns, and sums Sales. The report sheet is deliberately rebuilt each time; do not store other work on that sheet.

Option Explicit

Sub CreateSalesPivot()
    Dim wb As Workbook
    Dim dataWs As Worksheet
    Dim reportWs As Worksheet
    Dim sourceRange As Range
    Dim cache As PivotCache
    Dim pivot As PivotTable
    Dim sourceAddress As String
    Dim oldAlerts As Boolean

    Set wb = ThisWorkbook
    oldAlerts = Application.DisplayAlerts
    On Error GoTo HandleError

    ' Use the continuous data block beginning at A1.
    Set dataWs = wb.Worksheets("Data")
    Set sourceRange = dataWs.Range("A1").CurrentRegion

    If sourceRange.Rows.Count < 2 Then
        Err.Raise vbObjectError + 1000, , "Add at least one data row below the headers."
    End If

    ' Replace the previous output sheet, if it exists.
    Application.DisplayAlerts = False
    On Error Resume Next
    wb.Worksheets("PivotReport").Delete
    On Error GoTo HandleError
    Application.DisplayAlerts = oldAlerts

    Set reportWs = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
    reportWs.Name = "PivotReport"

    ' Use a workbook-qualified address as the PivotCache source.
    sourceAddress = sourceRange.Address(ReferenceStyle:=xlR1C1, External:=True)
    Set cache = wb.PivotCaches.Create(SourceType:=xlDatabase, _
                                      SourceData:=sourceAddress)
    Set pivot = cache.CreatePivotTable( _
        TableDestination:=reportWs.Range("A3"), _
        TableName:="SalesPivot")

    ' These captions must match the source headers exactly.
    With pivot
        .PivotFields("Region").Orientation = xlRowField
        .PivotFields("Category").Orientation = xlColumnField
        .AddDataField .PivotFields("Sales"), "Total Sales", xlSum
    End With

    reportWs.Range("A1").Value = "Sales by Region and Category"
    reportWs.Columns.AutoFit
    MsgBox "PivotTable created on PivotReport.", vbInformation
    Exit Sub

HandleError:
    Application.DisplayAlerts = oldAlerts
    MsgBox "PivotTable was not created: " & Err.Description, vbExclamation
End Sub

Adjust the source and field names if necessary. If your data is not on a sheet called Data, change Worksheets("Data") to its exact tab name. Replace Region, Category, and Sales with the exact header captions in your source. For example, a header called Net Revenue must be written as .PivotFields("Net Revenue"). Keep the quotes and parentheses, and make sure the field used for summing contains numbers.
Run the macro. Return to Excel and press Alt+F8 on Windows, select CreateSalesPivot, and choose Run. On Mac, open Tools > Macro > Macros, select the macro, and run it; keyboard shortcuts can differ by keyboard and Excel settings. If Excel displays a security warning, only enable macros if you trust the workbook and its code.
Review the result and refresh when needed. The new PivotReport sheet should contain a PivotTable starting at A3, with regions down the rows, categories across the columns, and summed sales values. If you later change the source records, run the macro again to rebuild the report. This version deletes and recreates the report sheet, so it also discards any notes or other content placed there.

Common Mistakes

Header captions do not match. VBA looks up PivotFields by their source header text. A small difference such as Sales Amount versus Sales causes an error. Copy the heading from the worksheet and use that exact text in the code.

The source range is not one clean block. CurrentRegion stops at blank rows and columns. A blank column in the middle can exclude data; unrelated content adjacent to the table can be included. Keep the source contiguous, or replace the CurrentRegion line with a deliberately selected range that includes all headers and records.

Numbers are stored as text. A column that looks numeric but contains text values may be counted rather than summed, or may not aggregate as expected. Convert the source values to numbers, then run the macro again. Check the PivotTable’s value field caption and aggregation as well.

The macro is run from the wrong workbook. ThisWorkbook means the workbook containing the VBA code, not necessarily the workbook currently selected on screen. Put the macro in the same workbook as the Data sheet, or change the workbook reference intentionally.

Important content is saved on PivotReport. The sample deletes that sheet before rebuilding it. Keep the report output disposable, or change the code to use a different output strategy before running it on a workbook with valuable content there.

Tips

For data that grows regularly, convert the source block into an Excel Table using Insert > Table, and give the table a clear name under Table Design > Table Name. Tables expand as you add records, which is often more reliable than a fixed cell range. The sample uses CurrentRegion, so it can include newly added records when they remain contiguous with A1; check the selected source boundaries when your layout changes.

Use descriptive field names and keep source data separate from presentation formatting. Put filters, totals, and charts on the report sheet rather than inserting blank rows or notes into the source block. You can add more row or column fields by setting additional PivotFields to xlRowField or xlColumnField. Set a field to xlPageField to make it a report filter. Keep field captions unique so the code can identify them unambiguously.

If you want counts instead of sums, change xlSum in the AddDataField line to xlCount. For dates, Excel may group values depending on the PivotTable and source data; format the source dates consistently and review the output after creation. For a production macro, consider adding explicit checks for required headers and numeric values, and provide a clear message if an expected field is missing.

Troubleshooting

If Excel reports that the subscript is out of range, check that the sheet tab is spelled exactly Data and that the macro is stored in the workbook containing that sheet. If it says a PivotField name is invalid or cannot be found, compare each field caption in the code with the first-row headers, including spaces and punctuation. The message box in the sample displays Excel’s error description; use it to narrow down the failing step.

If you get a PivotTable creation or source-data error, confirm that row 1 contains headers, at least one data row exists, and the source range does not contain merged cells or entirely blank headings. Confirm that PivotReport is not blocked by workbook structure protection, and that the workbook structure allows worksheets to be deleted and added. If the sales field is summarized as a count, convert its source values to numeric values and run the macro again.

If macros do not appear in the macro list, check that the code is in a standard module rather than a worksheet code window, and that the file is saved as .xlsm. If Excel blocks the macro, review the security notice and workbook source rather than lowering security settings indiscriminately. If the macro fails after deleting the old report, the source sheet remains intact; correct the issue and run it again. Restore a saved copy if the report sheet contained anything you needed.

Frequently Asked Questions

Can VBA create a PivotTable from multiple worksheets?
Yes, but this sample is designed for one contiguous list on one worksheet. Combining separate ranges generally requires preparing a unified source or using Excel’s Data Model and a more specialized macro. Do not simply point this code at several unrelated ranges.

Can the macro update an existing PivotTable instead of recreating it?
Yes. You can retain the existing report and cache, then refresh the PivotTable with its RefreshTable method. That requires managing changes to the source range and handling existing fields. The sample instead recreates its output sheet to keep the basic example repeatable.

Why does the PivotTable count Sales instead of summing it?
Excel may treat values as text or encounter blanks and nonnumeric entries in the source field. Convert the Sales column to numeric values, check for inconsistent entries, and run the macro again. Also confirm that the code specifies xlSum.

Will this VBA macro run in Excel for the web?
No. Excel for the web can preserve a macro-enabled workbook, but it cannot run or edit VBA. Open the file in the Excel desktop app to use the Visual Basic Editor and run the macro.

Does the macro work on Mac?
VBA is available in desktop Excel for Mac, and the PivotTable object model is broadly similar. Ribbon locations and keyboard shortcuts differ, and macro security settings may be managed differently. Use the Mac-specific editor and macro paths above, and verify the workbook in your installed Excel version.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.