How to create a PivotTable on a new worksheet using VBA?

Creating a PivotTable on a new worksheet using VBA involves several steps. In the following VBA code example, I’ll walk you through creating a PivotTable from a data set on an existing worksheet to a new worksheet.

This example assumes you have data on a worksheet named “Sheet1” with headers in Row 1 and data starting from cell A1. You will need to adjust the range and sheet names according to your data setup.

Sub CreatePivotTable()

    Dim wsData As Worksheet
    Dim wsPivot As Worksheet
    Dim dataRange As Range
    Dim pivotTable As PivotTable
    Dim pivotCache As PivotCache
    Dim pivotTableName As String

    ' Set the worksheet that contains the data
    Set wsData = ThisWorkbook.Sheets("Sheet1")

    ' Define the range of the data to be used in the PivotTable
    ' Assuming your data begins at A1 and extends to the last used row and column
    Set dataRange = wsData.UsedRange

    ' Add a new worksheet for the PivotTable
    Set wsPivot = ThisWorkbook.Sheets.Add(After:=wsData)
    wsPivot.Name = "PivotTableSheet"

    ' Create the Pivot Cache
    Set pivotCache = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=dataRange)

    ' Define the PivotTable name
    pivotTableName = "PivotTable1"

    ' Create the PivotTable on the new sheet
    Set pivotTable = wsPivot.PivotTables.Add( _
        PivotCache:=pivotCache, _
        TableDestination:=wsPivot.Cells(1, 1), _
        TableName:=pivotTableName)

    ' Add fields to the PivotTable for demonstration purposes
    ' You will need to adjust the field names based on your data
    With pivotTable
        .PivotFields("Field1").Orientation = xlRowField
        .PivotFields("Field2").Orientation = xlColumnField
        .PivotFields("Field3").Orientation = xlDataField
        ' You can add more fields as needed
    End With

    ' Format the PivotTable for better visibility
    pivotTable.ShowTableStyleRowStripes = True
    pivotTable.TableStyle2 = "PivotStyleMedium9"

    MsgBox "PivotTable created on a new worksheet."

End Sub

Key Points:

Data Range: Define the range of your data correctly. This example uses `wsData.UsedRange` to cover all occupied cells.
Field Names: Replace `”Field1″`, `”Field2″`, and `”Field3″` with the actual field names from your data.
Table Style: Adjust the `TableStyle2` property to apply different styles to the PivotTable.
Workbook Reference: If using this code in an add-in or a different workbook than the active one, ensure correct references.

Compile and run this macro in the VBA editor, and it should create a new worksheet with your PivotTable. Adjust field names and other parameters as needed.

facebooklinkedin