How to set the default layout for a PivotTable using VBA?

To set the default layout for a PivotTable using VBA, you can adjust properties of the PivotTable object as you create it. A common requirement is to set the PivotTable to show data in the Tabular form, display all items (even if they have no data), and potentially remove subtotals and grand totals. Below is an example of how you can achieve this using VBA:

Sub SetDefaultPivotTableLayout()
    Dim ws As Worksheet
    Dim dataSheet As Worksheet
    Dim ptCache As PivotCache
    Dim pt As PivotTable
    Dim dataRange As Range
    
    ' Set worksheet references
    Set ws = ThisWorkbook.Worksheets("PivotTableSheet") ' Change this to your Pivot Table Sheet Name
    Set dataSheet = ThisWorkbook.Worksheets("DataSheet") ' Change this to your Data Source Sheet Name
    Set dataRange = dataSheet.Range("A1").CurrentRegion ' Change this to your Data Source Range

    ' Clear existing data on PivotTable destination sheet
    ws.Cells.Clear

    ' Create Pivot Cache
    Set ptCache = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=dataRange)

    ' Create Pivot Table
    Set pt = ptCache.CreatePivotTable( _
        TableDestination:=ws.Range("A3"), _
        TableName:="MyPivotTable")

    ' Set PivotTable layout to Tabular Form
    With pt
        .RowAxisLayout xlTabularRow
        .RepeatAllLabels xlRepeatLabels
        .ShowDrillIndicators = False
        .DisplayErrorString = True
        .ErrorString = "-"
        
        ' Remove Subtotals
        Dim field As PivotField
        For Each field In .PivotFields
            field.Subtotals(1) = False
            field.Subtotals(1) = False
        Next field
        
        ' Remove Grand Totals
        .ColumnGrand = False
        .RowGrand = False

        ' Optional: Set other properties
        .NullString = ""
        .PreserveFormatting = True
    End With

    ' Add fields
    ' Example, you need to adjust these as per your data
    With pt
        .PivotFields("Category").Orientation = xlRowField
        .PivotFields("Product").Orientation = xlColumnField
        .PivotFields("Sales").Orientation = xlDataField
    End With
End Sub

Key Components of the Script:

Set References: The script sets references for the worksheet where the PivotTable will be placed and where the data is located.
Create Pivot Cache and PivotTable: This section creates a new PivotTable cache and the PivotTable itself.
Configure Layout: The layout is set to Tabular using `.RowAxisLayout xlTabularRow`.
Remove Subtotals: Each field’s subtotals are turned off using a loop.
Remove Grand Totals: Grand totals for rows and columns are turned off by setting `.ColumnGrand` and `.RowGrand` to `False`.
Customize Fields: Demo code shows how to add fields, which you must adjust according to your data structure.

Adjust this script according to your specific setup, such as sheet names, data range, and desired fields in the PivotTable.

facebooklinkedin