How to create a PivotTable using VBA?

Creating a PivotTable using VBA in Excel involves several steps. Here’s a step-by-step guide to help you through the process:


Sub CreatePivotTable()

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

    ' Set references for the data worksheet and pivot worksheet
    Set wsData = ThisWorkbook.Sheets("Sheet1")
    Set wsPivot = ThisWorkbook.Sheets("Sheet2")
    
    ' Define the range for source data
    Set dataRange = wsData.Range("A1").CurrentRegion
    
    ' Create a PivotCache from the data range
    Set pivotCache = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=dataRange)
    
    ' Define the destination range for the PivotTable
    Dim pivotDestination As Range
    Set pivotDestination = wsPivot.Range("A3") ' You can change this
    
    ' Create the PivotTable
    Set pivotTable = pivotCache.CreatePivotTable( _
        TableDestination:=pivotDestination, _
        TableName:="MyPivotTable")
    
    ' Customize the PivotTable: RowFields, ColumnFields, DataFields
    With pivotTable
        .PivotFields("Field1").Orientation = xlRowField
        .PivotFields("Field2").Orientation = xlColumnField
        .PivotFields("Field3").Orientation = xlDataField
        .PivotFields("Field3").Function = xlSum ' or use other functions like xlCount, xlAverage
        .PivotFields("Field3").NumberFormat = "#,##0"
    End With
    
End Sub

Open the Visual Basic for Applications (VBA) Editor:

Press `ALT + F11` to open the VBA editor in Excel.
If you don’t have a module already, insert a new module by right-clicking on any existing modules on the left navigation pane (under your workbook name) and selecting `Insert > Module`.

Write the VBA Code:

The following is a sample VBA code to create a PivotTable. This code assumes you have some data in a worksheet named “Sheet1” starting from cell A1, and you want to create a PivotTable on “Sheet2”.

Customize the VBA Code:

Ensure you adjust `”Field1″`, `”Field2″`, and `”Field3″` to the actual headers in your dataset.
Modify the `wsData`, `wsPivot`, `dataRange`, and `pivotDestination` as needed to reflect your workbook setup.

Run the VBA Code:

Close the VBA editor.
Back in Excel, press `ALT + F8` to open the Macro dialog box.
Select `CreatePivotTable` and click `Run`.

Check the PivotTable:

Navigate to “Sheet2” (or the sheet you set in your code) and check the created PivotTable.
Modify and format the PivotTable manually if additional customization is needed.

Remember, this code is quite basic; you can expand and adapt it to include more complex structures, error handling, and dynamic features depending on your specific requirements.

facebooklinkedin