Create a Pivot Table in Excel Using VBA

To create a PivotTable with VBA, prepare a rectangular data range with clear headers, then use a PivotCache and CreatePivotTable in a macro. The example below summarizes sales by region and product in a separate worksheet. You can adapt the field names and output location to match your workbook.

Before running the macro, save a copy of your workbook: macros can change or overwrite data, and their actions cannot be undone with Ctrl+Z. This guide is for desktop Excel for Microsoft 365 on Windows. Excel for Mac also supports VBA, though some menus and keyboard shortcuts differ. Excel for the web cannot run VBA macros.

What You’ll Need

A desktop version of Excel for Microsoft 365 and a workbook containing the source data.
A contiguous data range: one header row followed by records, with no completely blank rows or columns splitting the range.
Headers named Region, Product, and Sales for the example macro. Your headers can be different if you update the matching names in the code.
Worksheets named Data and Pivot. The output area beginning at cell A3 on Pivot should be empty and not overlap another PivotTable.
A macro-enabled workbook saved as .xlsm if you want to keep the VBA code in the file.

Step-by-Step

Prepare the source data. Put your headers in row 1 of the Data worksheet, with one record per row below them. For example, each record might contain a region, a product, and a sales amount. Use a distinct, nonblank name for every header. Make sure Sales values are numbers rather than numbers stored as text, and remove merged cells from the source range. These details help Excel identify fields and calculate totals correctly.
Create the output worksheet. Add a worksheet named Pivot, or rename an unused sheet to Pivot. Leave the area around A3 clear. The macro places the report with its top-left corner at A3, so do not put notes, formulas, or another PivotTable in the space where its output will expand. If you choose a different worksheet or starting cell, change the corresponding worksheet name or cell reference in the code.
Save a copy as a macro-enabled workbook. Use File > Save As and select Excel Macro-Enabled Workbook (*.xlsm) as the file type. If you are experimenting, keep an untouched copy of the original data as well. VBA changes are not reversed by Excel’s Undo command, so a separate copy is the simplest way to recover if a macro changes something you wanted to keep.
Open the Visual Basic Editor. In Windows, press Alt+F11. If the Developer tab is visible, you can also choose Developer > Visual Basic. On a Mac, use Developer > Visual Basic; the shortcut may be Option+F11 or Fn+Option+F11 depending on your keyboard. If the Developer tab is missing, enable it in Excel’s ribbon settings, or use the available Visual Basic Editor command in your version of Excel.
Insert a standard module. In the Visual Basic Editor, select your workbook in the Project pane. Choose Insert > Module. A blank code window opens. Paste the complete macro below into that window. The macro uses ThisWorkbook, so it looks for the Data and Pivot worksheets in the workbook that contains the code.

Option Explicit

Sub CreateSalesPivot()
    Dim wb As Workbook
    Dim wsData As Worksheet
    Dim wsPivot As Worksheet
    Dim sourceRange As Range
    Dim sourceAddress As String
    Dim pivotCache As PivotCache
    Dim pivotTable As PivotTable

    ' Use the workbook that contains this macro.
    Set wb = ThisWorkbook
    Set wsData = wb.Worksheets("Data")
    Set wsPivot = wb.Worksheets("Pivot")

    ' Capture the contiguous block around the header in A1.
    Set sourceRange = wsData.Range("A1").CurrentRegion
    If sourceRange.Rows.Count < 2 Then
        MsgBox "The source needs a header row and at least one data row."
        Exit Sub
    End If

    ' Use an external R1C1 address as the PivotCache source.
    sourceAddress = sourceRange.Address( _
        ReferenceStyle:=xlR1C1, External:=True)
    Set pivotCache = wb.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=sourceAddress)

    ' Build the report at A3 on the Pivot worksheet.
    Set pivotTable = pivotCache.CreatePivotTable( _
        TableDestination:=wsPivot.Range("A3"), _
        TableName:="SalesPivot")

    With pivotTable
        .PivotFields("Region").Orientation = xlRowField
        .PivotFields("Region").Position = 1
        .PivotFields("Product").Orientation = xlColumnField
        .PivotFields("Product").Position = 1
        .AddDataField .PivotFields("Sales"), "Total Sales", xlSum
        .DataFields(1).NumberFormat = "$#,##0.00"
    End With

    MsgBox "PivotTable created on the Pivot worksheet."
End Sub

Check the code against your workbook. The worksheet names, header names, destination cell, and PivotTable name must match your setup. The sample requires a header spelled Region, Product, and Sales. The Sales field needs numeric values because the code asks Excel to sum it. If your source begins somewhere other than A1, change the Range("A1") reference to a cell inside the data block. Keep the destination cell outside that block.
Run the macro. Return to Excel. In Windows, press Alt+F8, select CreateSalesPivot, and choose Run. On a Mac, open Tools > Macro > Macros, select the macro, and run it; menu names can vary slightly by Excel release. If Excel displays a security warning, only enable macros for a workbook whose source you trust. The macro creates a PivotTable with Region down the rows, Product across the columns, and summed Sales as the values.
Review and format the result. Check that the totals and labels make sense, then use the PivotTable tabs and field list to change the layout or apply filters. The code formats the value field as currency, but you can choose another number format if appropriate. Save the workbook after confirming the result. If you edit the source data later, refresh the PivotTable; the macro creates the report but does not automatically rerun each time the source changes.

The example produces a cross-tab report: each row represents a region, each column represents a product, and each intersecting value is the total Sales for that combination. Excel also adds row and column totals according to the PivotTable layout. If you would rather see products listed beneath regions, remove the Product column-field assignment and make Product a second row field instead.

Common Mistakes

Header text does not match. A missing or differently named Region, Product, or Sales header prevents the macro from finding that field. Use the exact header labels in the source, or edit the quoted field names in the VBA.
The source block is incomplete. CurrentRegion stops at fully blank rows or columns. A blank line inside the data can leave later records out of the PivotTable. Remove separator rows or columns, and keep the data as one uninterrupted rectangle.
The destination is occupied. A report may fail if A3 is inside another PivotTable or if the new PivotTable overlaps existing content. Choose a clear worksheet and destination cell. For a rerun, remove the previous PivotTable first or use a new output sheet; the sample does not delete an existing report.
Sales values are text. If Excel cannot treat the field as numeric, the report may count records instead of summing amounts, or may not offer the expected summary. Convert the source values to numbers and check for stray spaces or symbols.
The macro is saved in another workbook. ThisWorkbook refers to the file containing the VBA, not necessarily the workbook currently active on screen. Store the code in the workbook that holds Data and Pivot, or deliberately change the workbook reference after understanding the effect.

Tips

For a frequently updated dataset, consider converting the source range to an Excel Table with Insert > Table. Tables expand when you add records and make a good source for PivotTables. If the table grows after the macro runs, refresh the report and confirm that the PivotTable’s source includes the new rows; a PivotTable does not always extend its original source automatically.
Choose field placements based on the question. Put categories you want to compare vertically in Rows, categories to compare horizontally in Columns, and numeric measures in Values. Use Filters when readers need to narrow the report without changing its basic structure.
Use a descriptive PivotTable name that is unique within the workbook. If you change SalesPivot, follow Excel’s naming rules and avoid using a name already assigned to another PivotTable.
To summarize something other than a sum, change xlSum to a supported PivotField function such as xlCount or xlAverage. Choose a summary that fits the data; averaging transaction-level values, for example, answers a different question from adding them.
For a reusable process, keep source preparation, PivotTable creation, and formatting in distinct sections of your macro. That makes it easier to diagnose which part failed and to adapt the report when the source layout changes.

Troubleshooting

“Subscript out of range” appears. VBA cannot find a worksheet with the name it expects. Check the sheet tabs for spelling and extra spaces, then update Worksheets("Data") or Worksheets("Pivot") to match exactly. Also confirm that the macro is stored in the workbook containing those sheets, because the example uses ThisWorkbook.

Excel says the PivotTable field name is not valid. Inspect the header row for blanks, merged cells, duplicate names, or unexpected spaces. Each column needs a usable header. Then compare the header text with the names passed to PivotFields in the macro. If your data has a title or notes above the headers, change the source reference so the range begins on the actual header row.

The report appears in the wrong place or omits records. Check the source sheet and the cell used by CurrentRegion. The code begins at A1 and captures the connected block around it. If your headers begin elsewhere, point the code to a cell within the data. If blank rows or columns divide the records, remove them or revise the code to define the complete source range explicitly.

The macro does not appear in the macro list. Confirm the code is in a standard module and that the procedure is a Public Sub with no required arguments, as in the example. Save as .xlsm, close and reopen the file if needed, and enable macros only when you trust the workbook. Excel for the web does not run VBA; open the file in desktop Excel instead.

It fails when you run it a second time. The first PivotTable may still occupy the destination or the name SalesPivot may already exist. Delete the old PivotTable using Excel’s PivotTable commands, select a fresh output area, or revise the macro to manage the existing report. Do not simply clear cells inside a PivotTable while leaving its object in place.

Frequently Asked Questions

Can I create a PivotTable with VBA without using the mouse?
Yes. Once the code is in a standard module, you can run it from the Macros dialog or call it from another VBA procedure. You still need to prepare the workbook and ensure that the macro is permitted to run.

Can I use this macro on Excel for Mac?
Excel for Mac supports VBA and PivotTables, so the core object-model approach can work there. The Visual Basic Editor and macro-launch menus differ from Windows, and keyboard shortcuts depend on the Mac keyboard. The example avoids Windows-only ActiveX controls.

Can I run VBA in Excel for the web?
No. Excel for the web can open and edit many workbooks containing VBA, but it cannot create or run VBA macros. Use desktop Excel for Microsoft 365 on Windows or Mac to run this code.

How do I add another field to the report?
Use another PivotFields reference and set its Orientation to xlRowField, xlColumnField, or xlPageField for a filter. For a numeric measure, call AddDataField with the field and the summary function you want. Make sure the field name exactly matches a source header.

Will the PivotTable update when my source data changes?
Not automatically in every situation. Refresh the PivotTable after editing the source. If new rows are added, check that the PivotTable source includes them; using an Excel Table as the source can make expansion easier to manage.

Can I change the totals from sum to count?
Yes. In the sample, replace xlSum in the AddDataField line with xlCount to count nonblank entries in the selected field. For averages, use xlAverage. Check the report afterward to confirm the chosen calculation answers your question.

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.