Creating a dynamic Pivot Table with Excel VBA

Overview:

A dynamic Pivot Table is one that automatically adjusts its data source when new data is added. This is especially useful for reports that get updated frequently. We’ll use VBA to create a Pivot Table, define its dynamic data source, and customize it to display various fields in a Pivot Table format.

Code Explanation:

Sub CreateDynamicPivotTable()
    ' Declare variables
    Dim wsSource As Worksheet
    Dim wsPivot As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim pivotRange As Range
    Dim pivotTable As PivotTable
    Dim pivotCache As PivotCache
    Dim pivotSheetName As String   
    ' Define the source data sheet
    Set wsSource = ThisWorkbook.Sheets("Sheet1") ' Change to your data sheet name   
    ' Find the last row and column with data in the source sheet
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row ' Assumes data starts in column A
    lastCol = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column ' Assumes header row is in row 1   
    ' Set the dynamic data range for the pivot table (including headers)
    Set pivotRange = wsSource.Range(wsSource.Cells(1, 1), wsSource.Cells(lastRow, lastCol))   
    ' Check if a Pivot Table sheet already exists, and delete if found
    On Error Resume Next
    Set wsPivot = ThisWorkbook.Sheets("PivotTableSheet") ' Change to your desired pivot sheet name
    On Error GoTo 0
    If Not wsPivot Is Nothing Then
        Application.DisplayAlerts = False
        wsPivot.Delete
        Application.DisplayAlerts = True
    End If   
    ' Create a new worksheet for the Pivot Table
    Set wsPivot = ThisWorkbook.Sheets.Add
    wsPivot.Name = "PivotTableSheet"   
    ' Create a Pivot Cache from the data source
    Set pivotCache = ThisWorkbook.PivotTableWizard(wsSource:=pivotRange)   
    ' Create the Pivot Table in the new worksheet
    Set pivotTable = wsPivot.PivotTables.Add(PivotCache:=pivotCache, TableDestination:=wsPivot.Cells(1, 1), TableName:="DynamicPivotTable")   
    ' Add fields to the Pivot Table (example)
    ' Row Fields
    pivotTable.PivotFields("Category").Orientation = xlRowField
    pivotTable.PivotFields("Category").Position = 1   
    ' Column Fields
    pivotTable.PivotFields("Region").Orientation = xlColumnField
    pivotTable.PivotFields("Region").Position = 1   
    ' Data Fields
    pivotTable.PivotFields("Sales").Orientation = xlDataField
    pivotTable.PivotFields("Sales").Function = xlSum
    pivotTable.PivotFields("Sales").NumberFormat = "#,##0"   
    ' Optional: Formatting and layout settings
    With pivotTable
        .RowAxisLayout xlTabularRow
        .ColumnGrand = True
        .RowGrand = True
    End With   
    ' Adjust the column width for better display
    wsPivot.Columns.AutoFit
End Sub

Detailed Explanation:

1. Declare Variables:
   • wsSource: The worksheet containing the raw data.
   • wsPivot: The worksheet where the Pivot Table will be placed.
   • lastRow and lastCol: To determine the size of the data range.
   • pivotRange: The range of data to be used for the Pivot Table.
   • pivotCache: A cache that holds the Pivot Table data.
   • pivotTable: The actual Pivot Table object.
2. Set the Data Range:
   • We calculate the lastRow and lastCol to dynamically adjust the data range as the data changes (e.g., more rows are added).
3. Delete Existing Pivot Table Sheet:
   • We check if a sheet named « PivotTableSheet » exists. If it does, we delete it before creating a new one. This ensures you don’t have duplicate Pivot Tables.
4. Create a New Worksheet for the Pivot Table:
   • A new worksheet is created where the Pivot Table will be placed. It is named « PivotTableSheet ».
5. Create a Pivot Cache:
   • We create a Pivot Cache from the dynamic range of data. This allows the Pivot Table to pull data from the source sheet efficiently.
6. Add Fields to the Pivot Table:
   • You can define the rows, columns, and data fields in the Pivot Table. In this example:
     • « Category » is used as a Row Field.
     • « Region » is used as a Column Field.
     • « Sales » is used as a Data Field, and the sum of sales is displayed.
7. Formatting and Layout Settings:
   • The layout is set to xlTabularRow to display the rows in a tabular format.
   • Row and column totals are enabled using .RowGrand and .ColumnGrand.
8. AutoFit Columns:
   • Finally, the columns in the Pivot Table are auto-fitted for better presentation.

Customizing:

• Change the data range based on the columns in your actual data.
• Modify the row, column, and data fields based on your requirements.
• Adjust the Function of the Data Field if you want something other than the sum (e.g., xlAverage, xlCount).

Conclusion:

This code dynamically creates a Pivot Table based on a range of data in Excel, allowing you to update the report without manually changing the data range each time new data is added.