Dynamically Create and Modify Pivot Tables with Excel VBA

This includes code for creating a pivot table, modifying its layout, and updating its data source.

Explanation

A Pivot Table in Excel is a tool that allows you to summarize, analyze, explore, and present large datasets in a meaningful way. Through VBA, we can automate the creation and modification of Pivot Tables.

Steps we’ll cover in this VBA example:

1. Setting the Data Range: Define the range from which data will be used for the Pivot Table.
2. Creating a Pivot Table: Dynamically create a new Pivot Table from the specified range.
3. Modifying the Pivot Table: Add or remove fields from the Pivot Table dynamically.
4. Refreshing the Pivot Table: Update the Pivot Table when the data changes.

We’ll use the PivotTableWizard or PivotTable.Add method, which allows us to control how the Pivot Table is structured, including where fields are placed (rows, columns, values, filters).

VBA Code for Dynamically Creating and Modifying a Pivot Table

Sub CreateAndModifyPivotTable()
    ' Declare necessary variables
    Dim wsData As Worksheet
    Dim wsPivot As Worksheet
    Dim ptCache As PivotCache
    Dim pt As PivotTable
    Dim dataRange As Range
    Dim pivotRange As Range
    Dim lastRow As Long
    Dim lastCol As Long
    ' Set the worksheet containing the data
    Set wsData = ThisWorkbook.Worksheets("Sheet1")
    ' Find the last row and column of the data
    lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
    lastCol = wsData.Cells(1, wsData.Columns.Count).End(xlToLeft).Column
    ' Define the data range (assuming the data starts from A1)
    Set dataRange = wsData.Range(wsData.Cells(1, 1), wsData.Cells(lastRow, lastCol))
    ' Create a new worksheet for the Pivot Table (if not already created)
    On Error Resume Next
    Set wsPivot = ThisWorkbook.Worksheets("PivotSheet")
    On Error GoTo 0
    If wsPivot Is Nothing Then
        Set wsPivot = ThisWorkbook.Worksheets.Add
        wsPivot.Name = "PivotSheet"
    End If
    ' Clear any existing PivotTable in the new PivotSheet
    wsPivot.Cells.Clear
    ' Create a Pivot Cache from the data range
    Set ptCache = ThisWorkbook.PivotTableWizard(dataRange)
    ' Create a new Pivot Table from the cache
    Set pt = wsPivot.PivotTableWizard(SourceType:=xlDatabase, SourceData:=dataRange)
    ' Position the Pivot Table at cell A1 in the PivotSheet
    Set pivotRange = wsPivot.Range("A1")
    pt.TableRange2.Cut Destination:=pivotRange
    ' Modify the Pivot Table: Adding fields
    With pt
        ' Add 'Product' to Rows
        .PivotFields("Product").Orientation = xlRowField
        .PivotFields("Product").Position = 1
        ' Add 'Region' to Columns
        .PivotFields("Region").Orientation = xlColumnField
        .PivotFields("Region").Position = 1
        ' Add 'Sales' to Values (sum the sales data)
        .PivotFields("Sales").Orientation = xlDataField
        .PivotFields("Sales").Function = xlSum
        .PivotFields("Sales").NumberFormat = "#,##0"
        ' Add 'Date' as a Page Filter
        .PivotFields("Date").Orientation = xlPageField
        .PivotFields("Date").Position = 1
    End With
    ' Refresh the Pivot Table to reflect changes
    pt.RefreshTable
    ' Formatting the Pivot Table for better readability
    With pt.TableRange1
        .Font.Size = 10
        .Font.Name = "Calibri"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    ' Optional: Automatically adjust column widths
    wsPivot.Columns.AutoFit
    ' Inform the user that the pivot table is created
    MsgBox "Pivot Table Created and Modified Successfully!", vbInformation
End Sub

Code Breakdown

1. Declare Variables:
   • wsData: A worksheet variable that holds the data from which the Pivot Table will be created.
   • wsPivot: A worksheet variable to store the location where the Pivot Table will be created.
   • ptCache: A cache for the Pivot Table.
   • pt: A PivotTable object.
   • dataRange: A Range object representing the data to be summarized.
   • pivotRange: A Range object where the Pivot Table will be positioned.
2. Set Data Range:
   • The lastRow and lastCol determine the bounds of the data.
   • The dataRange object is defined using these bounds (starting from A1).
3. Create Pivot Sheet:
   • If the worksheet PivotSheet already exists, it’s reused. If not, a new one is created.
4. Create Pivot Table:
   • A Pivot Table Cache (ptCache) is created from the data range.
   • The Pivot Table is then created using the PivotTableWizard method.
5. Modify Pivot Table:
   • The fields are dynamically added:
     • Rows: The « Product » field is added to the Row area.
     • Columns: The « Region » field is added to the Column area.
     • Values: The « Sales » field is added to the Data area, summarizing with the SUM function.
     • Page Filters: The « Date » field is added to the filter area.
6. Formatting:
   • The Pivot Table’s font size and style are customized.
   • Column widths are automatically adjusted for better readability.
7. Refresh the Pivot Table:
   • After modifying the Pivot Table, pt.RefreshTable ensures that the latest changes are applied and displayed.
8. Message Box:
   • A confirmation message is displayed to inform the user that the Pivot Table has been created successfully.

Additional Modifications You Can Make:

• Change the Aggregation: You can change the aggregation of data in the values area by modifying Function = xlSum. For example, you can use xlAverage for averaging the data.
• Add More Filters: You can add more filters by adding more fields to the PageField section.
• Dynamic Range: You can make the data range dynamic by using TableRange or even querying a named range if your data source changes frequently.

Conclusion

This VBA code demonstrates how to create and modify a Pivot Table dynamically. You can adjust the field names and layout as required for different datasets. The code ensures flexibility, allowing you to adapt the structure and appearance of the Pivot Table to meet your needs.