How to access specific column totals for pivot table in VBA?

Question

This is a very trivial question but I am struggling to find a answer to this online.

How do I "access" a pivot table grand total for a given column.

For instance I have the pivot table "pivottable1".

Customer   Product Code   Sum of price
   .   
   .             .
   .                           . 
 GRAND TOTAL                   $5000

So in this example I want to copy the $5000 into another cell.

The pivot table is dynamic and will change length.

I want to say for a given column Sum of price, take the grand total and copy it to another cell.

Answer 1

Notes:

• Will print the value to A1
• Change the Field Name accordingly
• Will work for Columns.
• More info here Referencing Pivot Table Range

Try:

Sub tst()

With ActiveSheet.PivotTables("PivotTable1")

ActiveSheet.Range("A1").Value = .PivotFields("Sum of Sum").DataRange.End(xlDown).Value

End With

End Sub

Demo:

Answer 2

Give it a try:

Sub foo()
    Dim lngRows As Long, lngColumns As Long, lngGT As Long

    With Sheet1.PivotTables(1).DataBodyRange
        lngRows = .Rows.Count
        lngColumns = .Columns.Count
        lngGT = .Cells(1).Offset(lngRows - 1, lngColumns - 1)
    End With

    Sheet1.Range("E11").Value = lngGT
End Sub

Result: