How to create a PivotTable from a dynamic range using VBA?

Creating a PivotTable from a dynamic range using VBA involves several steps. Here’s a step-by-step guide to achieving this:

Define the Dynamic Range: Before creating the PivotTable, you need to determine the last row and column of your data to define the dynamic range.
Use VBA to Create the PivotTable: Once the range is defined, you can use VBA to create the PivotTable.

Here’s a sample VBA code that demonstrates how to create a PivotTable from a dynamic range:

Sub CreatePivotTableFromDynamicRange()
    Dim ws As Worksheet
    Dim wsPivot As Worksheet
    Dim dataRange As Range
    Dim pivotTableRange As Range
    Dim lastRow As Long
    Dim lastCol As Long
    Dim pivotTable As PivotTable
    Dim pivotCache As PivotCache
    Dim pivotTableName As String

    ' Your data is on Sheet1 (change as needed)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Find the last row of the data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Find the last column of the data
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' Set the dynamic data range
    Set dataRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
    
    ' Add a new worksheet for the PivotTable (optional)
    Set wsPivot = ThisWorkbook.Worksheets.Add
    wsPivot.Name = "PivotTableSheet" ' Change the name if needed
    
    ' Set the PivotTable range on the new worksheet
    Set pivotTableRange = wsPivot.Cells(1, 1)
    
    ' Create the PivotTable cache
    Set pivotCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=dataRange)
    
    ' Add the PivotTable to the worksheet
    pivotTableName = "MyPivotTable" ' Define a unique name for your PivotTable
    Set pivotTable = pivotCache.CreatePivotTable(TableDestination:=pivotTableRange, TableName:=pivotTableName)
    
    ' Optional: Configure fields
    With pivotTable
        ' Example: Set fields in the PivotTable (update as needed)
        .PivotFields("Field1").Orientation = xlRowField
        .PivotFields("Field2").Orientation = xlColumnField
        .PivotFields("Field3").Orientation = xlDataField
    End With
    
    MsgBox "PivotTable created successfully!"

End Sub

Explanation:

Dynamic Range: The script first identifies the dynamic range by finding the last row and column with data.
Worksheet Setup: It sets up a new worksheet where the PivotTable will be created (you can also place it on an existing sheet if preferred).
PivotCache and PivotTable Creation: A PivotCache is created from the specified data range, and a PivotTable is created from this cache.
Field Configuration: You can customize the fields in your PivotTable by changing the field names and orientations.

Notes:

Make sure to replace `”Sheet1″` and field names (`”Field1″`, `”Field2″`, etc.) with the appropriate names based on your data.
You may want to handle exceptions or test if the sheet already exists to avoid errors when adding a new sheet.
To execute this script, press `ALT + F11` to open the VBA Editor, insert a new module, and paste the code into the module. Then, run the macro from Excel to create the PivotTable.

facebooklinkedin