How to display grand totals in a PivotTable using VBA?

To display grand totals in a PivotTable using VBA in Excel, you can manipulate the `PivotTable` properties related to grand totals. Here’s a simple example of how you can create a PivotTable and ensure grand totals are displayed for both rows and columns:

Sub DisplayGrandTotalsInPivotTable()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim ptCache As PivotCache
    Dim dataRange As Range
    Dim pivotRange As Range
    Dim lastRow As Long
    Dim lastCol As Long

    ' Set the worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Change to your sheet name

    ' Determine the data range
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    Set dataRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))

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

    ' Determine the range for the PivotTable
    Set pivotRange = ws.Cells(1, lastCol + 2) ' Adjust placement as needed

    ' Create the PivotTable
    Set pt = ptCache.CreatePivotTable( _
                  TableDestination:=pivotRange, _
                  TableName:="MyPivotTable")

    ' Add fields to the PivotTable
    With pt
        .PivotFields("Field1").Orientation = xlRowField ' Replace with your field names
        .PivotFields("Field2").Orientation = xlColumnField
        .PivotFields("ValueField").Orientation = xlDataField
    End With

    ' Enable grand totals for rows and columns
    With pt
        .ColumnGrand = True
        .RowGrand = True
    End With
End Sub

Explanation:

Set up the Worksheet and Data Range: The script starts by identifying the worksheet and data range for which the PivotTable will be created.
Create a PivotCache: This object serves as the data source for the PivotTable.
Place the PivotTable: The destination for the PivotTable (`pivotRange`) is defined. You might need to adjust the destination cell based on your layout.
Configure Fields: Fields for rows, columns, and values are assigned. Replace `”Field1″`, `”Field2″`, and `”ValueField”` with your actual field names.
Enable Grand Totals: `ColumnGrand` and `RowGrand` properties of the `PivotTable` object are set to `True` to display grand totals for columns and rows.

Modify the script according to your specific needs, such as different field names or worksheet names. This code provides a basic framework for creating a PivotTable with grand totals using VBA.

facebooklinkedin