CODEWITHSUNDEEP
excelvba
DSM
DSM

Reputation: 257

Grand total pivot table range reference for excel VBA

I would like to simply get the value of the grand total of a pivot table. However I need this to be more dynamic than the solution found online.

enter image description here

A simple solution that I have seen online was to get the last cell found in the range of the grand total column. In this case, resulting to something like the code mentioned below.

Dim grandTotal As Range
grandTotal = Range("E65536").End(xlUp)
MsgBox grandTotal.Value

However, this would simply fail if I had some data filled in below the pivot table, in the same column. Is there a way to precisely reference the grand total value? Maybe like referencing the data range of the two grand total column and row, and find an intersect between the two to get the cell highlighted in yellow?

Edit:

What about getting the grand total for the two different data value columns

enter image description here

Upvotes: 1

Views: 9071

Answers (3)

Barbara_Munich
Barbara_Munich

Reputation: 1

I have used the following approach. My pivot table is as follows:

...
Dim strSourceData As String
strSourceData = "'SAP Dump'!R1C1:R" & Trim$(Str$(lr)) & "C9"

wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=strSourceData, _
         Version:=6).CreatePivotTable TableDestination:= _
        "Pivot!R2C2", TableName:="Pivot_Proj_MA", DefaultVersion:=6
    
Set pt = ws.PivotTables("Pivot_Proj_MA")
With pt.PivotFields("Receiver WBS Element")
    .Orientation = xlRowField
    .Position = 1
End With
With pt.PivotFields( _
    "Name of employee or applicant")
    .Orientation = xlRowField
    .Position = 2
End With

pt.AddDataField pt.PivotFields("Number (Unit)"), "Sum of Number (Unit)", xlSum
...

To get the sums, try this (from https://learn.microsoft.com/en-us/office/vba/api/excel.pivottable.getdata)

Set pt = ws.PivotTables("Pivot_Proj_MA")
Debug.Print pt.GetData("'Sum of Number (Unit)' K-020979-100 'HUBER ANDREAS'")
                               =                     =               =
                            DataField              RowField        RowField

If you use only the datafield, you get the sum of this field.

Debug.Print pt.GetData("'Sum of Number (Unit)'")

Upvotes: 0

Rodlima
Rodlima

Reputation: 11

If you need to reference different Rows or Columns Totals, this way should work well for you.

Sub SelectGrandTotal()
  Dim pt As PivotTable
  Dim rGrandTotal As Range
  
  Set pt = ActiveSheet.PivotTables(1)
  
  With pt
  
    'This condition checks if the GrandTotals are activated. Not really necessary in some cases.
    If .ColumnGrand And .RowGrand Then
      With .DataBodyRange
      
        'Add "- 1" after ".Count" if you want to move between different Totals.
        Set rGrandTotal = .Cells(.Rows.Count, .Columns.Count)
        rGrandTotal.Select
        'Print
        MsgBox (rGrandTotal)

      End With

    End If

  End With

End Sub

Upvotes: 0

JvdV
JvdV

Reputation: 75840

Well, since it would be the most bottom right cell in your pivot table:

Set pt = Activesheet.PivotTables(1)
grandTotal = pt.DataBodyRange.Cells(pt.DataBodyRange.Cells.Count).Value

Upvotes: 2

Related Questions