use GetPivotData()

from Help:

Example
In this example, Microsoft Excel returns the quantity of chairs in the
warehouse to the user. This example assumes a PivotTable report exists on the
active worksheet. Also, this example assumes that, in the report, the title
of the data field is "Quantity", a field titled "Warehouse" exists, and a
data item titled "Chairs" exists in the Warehouse field.

Sub UseGetPivotData()

Dim rngTableItem As Range

' Get PivotData for the quantity of chairs in the warehouse.
Set rngTableItem = ActiveCell. _
PivotTable.GetPivotData("Quantity", "Warehouse", "Chairs")

MsgBox "The quantity of chairs in the warehouse is: " & rngTableItem.Value

End Sub


"John" wrote:

How can I refer to the Grand Total row of a Pivot Table in VBA? For example,
if I wanted a sub to change the row height of the Grand Total row. (Using a
named range doesn't work because the range moves when the table is
refreshed). Thanks