VBA Create Pivot Table on a New Sheet: Steps and Code

Yes. In Excel for Microsoft 365 on Windows, VBA can use the selected data region to create a PivotTable on a newly added worksheet. The example below expects headers named Region, Product, and Sales; select a cell in the data, run the macro, and it builds a summary with regions in rows, products in columns, and summed sales as values.

What the macro creates

A PivotTable is an interactive summary of a source range. It groups records and calculates totals, counts, or other summaries without changing the original data. The macro creates a new worksheet, builds a PivotCache from the selected data region, and places the PivotTable at cell A3 on that sheet. Excel supplies the PivotTable’s name automatically.

The example is deliberately specific so it can produce a useful result immediately. It assumes the source has one header row and that the header text is exactly Region, Product, and Sales. Region becomes the row field, Product becomes the column field, and Sales becomes a value field summarized by sum. If your headers differ, change the three field-name strings in the code to match them exactly.

This is different from simply adding a worksheet and copying data to it: the new sheet contains a real PivotTable, so you can rearrange fields and change the summary. The source data stays on its original sheet.

Check the source data first

The macro treats the contiguous block around the active cell as the source. In practice, that means you should click any cell inside the dataset before running it. The block should have a single header row, one record per row, and no completely blank rows or columns breaking up the data. A blank line can cause CurrentRegion to stop early and omit records.

Make sure every source column has a distinct, nonblank header. PivotTables use those headers as field names.
Keep dates, quantities, and sales values in consistent data types. Numbers stored as text may not sum as expected.
Remove subtotals or extra title rows from the middle of the source block. They can be treated as ordinary records.
Save a copy of the workbook before running a macro. VBA actions cannot be reversed with Ctrl+Z.

For a reusable reporting process, consider converting the source to an Excel Table with Insert > Table before creating the PivotTable. Tables expand when you add records. The sample code below uses the current contiguous range, however, so its PivotTable source is the range captured when the macro runs.

Run the VBA code

In desktop Excel for Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the complete code below into the module, return to Excel, click a cell in the source data, and run CreateSalesPivotOnNewSheet. You can run it from the editor with F5 while the cursor is inside the procedure, or from Excel using Developer > Macros.

Option Explicit

Sub CreateSalesPivotOnNewSheet()
    Dim wb As Workbook
    Dim wsSource As Worksheet
    Dim wsPivot As Worksheet
    Dim sourceRange As Range
    Dim cache As PivotCache
    Dim pt As PivotTable

    ' Use the active workbook and the block around the selected cell.
    Set wb = ActiveWorkbook
    Set wsSource = ActiveSheet
    Set sourceRange = ActiveCell.CurrentRegion

    If sourceRange.Rows.Count < 2 Then
        MsgBox "Select a data range with a header and at least one record."
        Exit Sub
    End If

    ' Add a uniquely named sheet at the end of the workbook.
    Set wsPivot = wb.Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count))
    wsPivot.Name = GetUniqueSheetName(wb, "Pivot Summary")

    ' Build a cache from the selected source block.
    Set cache = wb.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=sourceRange.Address( _
            ReferenceStyle:=xlR1C1, External:=True))

    ' Create the PivotTable on the new sheet, starting at A3.
    Set pt = cache.CreatePivotTable( _
        TableDestination:=wsPivot.Range("A3"))

    ' These names must match the source column headers exactly.
    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
End Sub

Private Function GetUniqueSheetName( _
    ByVal wb As Workbook, ByVal baseName As String) As String
    Dim candidate As String
    Dim suffix As Long
    Dim sh As Object
    Dim nameInUse As Boolean

    candidate = baseName
    suffix = 2

    Do
        nameInUse = False
        For Each sh In wb.Sheets
            If StrComp(sh.Name, candidate, vbTextCompare) = 0 Then
                nameInUse = True
                Exit For
            End If
        Next sh

        If Not nameInUse Then Exit Do
        candidate = Left$(baseName, 31 - Len(CStr(suffix)) - 1) _
                    & " " & CStr(suffix)
        suffix = suffix + 1
    Loop

    GetUniqueSheetName = candidate
End Function

The function at the end checks for an existing sheet called Pivot Summary and chooses a numbered alternative if necessary. Excel worksheet names cannot exceed 31 characters, and the generated names stay within that limit. The code adds the new sheet before creating the cache and PivotTable; if the field names are wrong or the source data is unsuitable, Excel may show an error after the sheet has been added.

How the code works

ActiveCell.CurrentRegion finds the connected block of cells around the selection. That range is passed to PivotCaches.Create, which prepares the data for the PivotTable. A PivotCache is the PivotTable’s underlying snapshot of the source; creating a PivotTable from it does not move or delete the original records.

CreatePivotTable places the report at A3. The field orientation settings then assign Region to rows and Product to columns. AddDataField adds Sales as a measure and asks Excel to sum it. If the Sales column contains text rather than numeric values, the result might be a count or otherwise fail to represent the intended total. Correct the source values before relying on the summary.

The sample starts from the active cell, not from a hard-coded address such as A1. This makes it usable when the dataset begins elsewhere on the sheet. It still depends on a clean, contiguous block, so selecting a cell in a nearby note or separate table can produce an unintended source.

Change the fields or source range

To use different fields, replace the quoted names in the PivotFields calls with the exact header labels. For example, a source headed Salesperson, Category, and Revenue could use Salesperson as the row field, Category as the column field, and Revenue in AddDataField. The displayed caption, such as Total Sales, is the label shown for the values field; it does not have to match the source header.

To show categories vertically and regions horizontally, swap the row and column assignments. To count records instead of summing numeric sales, change the aggregation argument from xlSum to xlCount. Other summaries are available through Excel’s PivotField functions, but the suitable choice depends on the meaning and data type of the field.

If a source has more than one value field, add another AddDataField line using a different source header and caption. If you need a different source area, either select a cell within that area before running the macro or replace the CurrentRegion assignment with a specific Range expression. Hard-coded addresses are easy to understand but can miss new rows when the dataset grows.

Refreshes, new records, and Excel Tables

A PivotTable does not necessarily include rows added below a fixed source range just because you refresh it. Refresh recalculates from the source the PivotTable already knows about; it does not automatically guarantee that the source boundary has expanded. With the sample, running the macro again rebuilds the PivotTable using the current region, but it also creates another summary sheet.

For ongoing reports, an Excel Table is usually a better source because it expands as records are appended. You can create one using Insert > Table, confirm that the data has headers, and then base a PivotTable on that table. If you adapt the VBA to use a table name as its source, use the table’s actual name and ensure the cache accepts that source reference. For a one-time report, the selected-range approach is simpler.

After changing source values, refresh the PivotTable from PivotTable Analyze > Refresh. If you have added records outside the original source, first update the source range using PivotTable Analyze > Change Data Source, or rebuild the cache with an expanded or table-based source. Those steps are separate from the macro’s initial creation process.

Common errors and practical fixes

Cannot get the PivotFields property: Check that the header text in the code matches the source headers, including spaces. For example, Net Sales is not the same field name as Sales.
Excel creates an incomplete report: Check for blank rows or columns splitting the data block, then rerun with a cell inside the complete dataset selected.
The total is a count rather than a sum: Inspect the source column for numbers stored as text, blanks, or mixed values. Convert valid numeric entries to numbers and create or refresh the PivotTable again.
A duplicate-looking sheet appears: The macro intentionally creates a fresh worksheet each time. Delete an obsolete report manually or change the code to reuse a known destination sheet if that is the desired workflow.
The PivotTable does not show newly appended records: Refreshing alone may not expand a fixed source range. Change the source boundary or use a growing Excel Table as the source.

When troubleshooting, check the source headers and selection before changing the cache code. Many failures come from a mismatch between the actual column labels and the names in the PivotFields calls.

Macros, file types, and Mac differences

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm); saving it as .xlsx removes the VBA project. Excel may display a security warning when you open a macro-enabled file. Only enable macros when you trust the workbook and its source. Keep a backup before running code that changes sheets or workbook contents, because Ctrl+Z does not undo VBA operations.

Excel for the web cannot run VBA macros. On Mac, use desktop Excel and its Visual Basic Editor; the editor shortcut may be Option+F11, sometimes with the Fn key depending on the keyboard. Ribbon and editor layouts can differ from Windows, and some VBA features vary by platform. The basic PivotCache and PivotTable object model used here is intended for desktop Excel; check any platform-specific workbook restrictions if adapting the macro.

Frequently Asked Questions

Can VBA create a PivotTable on a new worksheet automatically?
Yes. Add a worksheet with Worksheets.Add, create a PivotCache from the source, and call CreatePivotTable with a destination range on the new sheet. The sample performs all three steps.

How do I create a PivotTable on a new sheet without VBA?
Select the source data, choose Insert > PivotTable, and in the dialog choose New Worksheet before confirming. This is a good option for a one-off report when you do not need automation.

Why does the macro say a PivotField does not exist?
The header named in the code probably differs from the source header, or the selected block does not include the intended header row. Match the field-name strings to the actual first-row labels.

Can the macro create a new sheet every time it runs?
Yes. This version adds a worksheet on each run and gives it a unique Pivot Summary name. It does not replace or delete previous reports.

Can I run this VBA macro in Excel online?
No. Excel for the web does not execute VBA. Open the macro-enabled workbook in desktop Excel to run the code.

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.