VB Reference to Grand Total row in Pivot Table

[John]

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

 

[Patrick Molloy]

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

 

[minimaster]

Not sure whether you want a reference to the row or just to the grand
totals. Here is some sample code for selecting the grand totals in two
different but similar ways.


Sub selectRowGrandTotals(Optional pt As PivotTable)
If pt Is Nothing Then Set pt = getPivotTable
If Not pt Is Nothing Then
Sheets(pt.Parent.Name).Select
RowGrandTotalsRange(pt).Select
Else
MsgBox "No pivot tables on the active sheet."
End If
End Sub

Sub selectColumnGrandTotals(Optional pt As PivotTable)
Dim X As Range
If pt Is Nothing Then Set pt = getPivotTable
If Not pt Is Nothing Then
Set X = pt.DataBodyRange
With pt
.ColumnGrand = True
End With
Sheets(pt.Parent.Name).Select
X.Range(Cells(X.Rows.Count, 1), Cells(X.Rows.Count,
X.Columns.Count)).Select
Else
MsgBox "No pivot tables on the active sheet."
End If
End Sub

Function RowGrandTotalsRange(Ptable As PivotTable) As Range
With Ptable
.RowGrand = True
End With
Set RowGrandTotalsRange = Ptable.DataBodyRange.Range( _
Sheets(Ptable.Parent.Name).Cells(1, _
Ptable.DataBodyRange.Columns.Count), _
Sheets(Ptable.Parent.Name).Cells
(Ptable.DataBodyRange.Rows.Count, _
Ptable.DataBodyRange.Columns.Count))
End Function