Create Dynamic Range Pivot Tables with Excel VBA

Creating dynamic range pivot tables with VBA involves setting up your data properly, defining a dynamic range (that expands or contracts based on your data), and then generating the pivot table. Here’s a detailed step-by-step guide, along with the code, to achieve this:

Step 1: Set Up Data

Ensure that your data is organized in a table-like format, where each column has a header, and the data below is consistent. For this example, let’s assume that the data is in a sheet named « DataSheet » and that the columns are Product, Region, Sales, and Date.

Example data layout:

Step 2: Define a Dynamic Range

The key to creating a dynamic range is to define the range based on the data in the worksheet. We can use Excel’s ListObject feature (tables) or dynamic ranges using VBA. Here’s how we can do this:

In this example, we will define the dynamic range using the Range object, and we will make use of the End(xlDown) or End(xlToRight) properties to find the last row and column of data.

Step 3: Create Pivot Table

With the dynamic range set, the next step is to create the pivot table. We’ll use the PivotTableWizard method or the newer PivotTable object, which offers more control.

VBA Code:

Sub CreateDynamicPivotTable()
    ' Step 1: Define the Worksheet and Data Range
    Dim wsData As Worksheet
    Set wsData = ThisWorkbook.Sheets("DataSheet")   
    ' Step 2: Create Dynamic Range using ListObject (Table) or a Range (Non-Table)
    Dim lastRow As Long
    Dim lastCol As Long
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row ' Get last row in column A
    lastCol = wsData.Cells(1, wsData.Columns.Count).End(xlToLeft).Column ' Get last column in row 1   
    ' Define the dynamic range from A1 to the last row and column
    Dim dataRange As Range
    Set dataRange = wsData.Range(wsData.Cells(1, 1), wsData.Cells(lastRow, lastCol))
    ' Step 3: Create a New Pivot Table on a New Worksheet
    Dim wsPivot As Worksheet
    Set wsPivot = ThisWorkbook.Sheets.Add
    wsPivot.Name = "PivotSheet"
    ' Step 4: Create the Pivot Table
    Dim pivotTable As PivotTable
    Set pivotTable = wsPivot.PivotTableWizard _
        (SourceType:=xlDatabase, SourceData:=dataRange, _
        TableDestination:=wsPivot.Cells(1, 1), _
        TableName:="SalesPivotTable")   
    ' Step 5: Customize the Pivot Table (optional)
    ' Example: Adding fields to Rows, Columns, and Values
    With pivotTable
        .PivotFields("Product").Orientation = xlRowField
        .PivotFields("Product").Position = 1
        .PivotFields("Region").Orientation = xlColumnField
        .PivotFields("Region").Position = 1
        .PivotFields("Sales").Orientation = xlDataField
        .PivotFields("Sales").Function = xlSum
        .PivotFields("Sales").NumberFormat = "#,##0"
    End With  
    MsgBox "Pivot Table Created Successfully!"
End Sub

Explanation of the Code:

1. Set Up Data and Define the Range:
   • The worksheet wsData is set to DataSheet where the raw data is stored.
   • lastRow and lastCol are calculated to determine the last row and column in the data. The dynamic range will extend from A1 to the last data cell based on these values.
2. Create a New Pivot Table:
   • A new worksheet wsPivot is created to host the pivot table.
   • The PivotTableWizard method is used to create the pivot table, with the source data being the dynamic range we defined.
3. Customizing the Pivot Table:
   • The pivot table fields are set for rows (Product), columns (Region), and values (Sales), where the Sales field is summarized using SUM.
   • The number format for the Sales field is also set to show numbers with commas.

Example Output:

After running this code, a new sheet called PivotSheet will be created, containing a pivot table that summarizes sales data by product and region.

Example output:

Notes:

• The dynamic range automatically adjusts if more data is added to the DataSheet.
• You can further customize the pivot table, like adding more filters or changing summary functions (e.g., Average, Count).