VBA How do I return the address of Grand Total in a Pivottable

[Ziggy12]

I need to find the grand total label in a pivottable. The cell where the heading Grand Total is.
The Grand Total will move depending on data in labels in preceeding column headings.

cheers
ziggy

 

[RoryA]

You can have a Grand Total cell for columns and for rows - this function will return whichever you specify (pass 1 for the second argument (or omit) for row totals, or 2 for column):

Function GetGrandTotalCell(pt As PivotTable, Optional RowOrCol As Integer = 1) As Range
    Dim rng As Range
    If RowOrCol = 1 Then
        Set rng = pt.RowRange
    ElseIf RowOrCol = 2 Then
        Set rng = pt.ColumnRange
    Else
        Exit Function
    End If
    With rng
        Set GetGrandTotalCell = .Cells(.Cells.Count)
    End With
End Function

 

[Ziggy12]

Have been giving this a go but what arguments are passed to the function and how.
The pivottable name is Pivottable 4 on sheet1, cell "B3"

cheers
ziggy

 

[RoryA]

You pass an actual pivot table object:

msgbox getgrandtotalcell(sheets("sheet1").pivottables("pivottable4"), 1)

for example

 

[Ziggy12]

Couldn't get it to work. Only returned GrandTotal not the address of the cell. Noticed that (.cells.count) had a value of 18 but that didn't seem to related to the pivot table.

Have selected a cell in the column feilds and incremented a long till it came to Grand Total and returned the active cell address.

ziggy

 

[RoryA]

It returns an actual range object, so if you added .address onto that code you would see the address.

 

[Ziggy12]

That got it
Thanks for your help

ziggy