PDA

View Full Version : Solved: Select Grand Total in Pivot Table



dragon576
10-13-2008, 06:41 AM
Hi,
I am trying to to replicate the following in VBA. I have a Pivot table, and when I click on one of the cells in the "Grand Total" row I get this action (it creates a new sheet with detail on it) if I record the action as a macro.

Range("C20").Select
Selection.ShowDetail = True

However as the amount of rows in the table is dynamic depending on the data, I need a way of always selecting the correct Grand Total Cell.

I have tried to use the following to select the cell, and although I can find the value in the cell and display in a msg box, (shown as comment), I can not select the variable within a range.

Sub Get_GTotalPT()
Dim rngPTTot As Range, rngPTData As Range
Dim x As Long, y As Integer


With ActiveSheet
Set rngPTData = .PivotTables(1).DataBodyRange
x = rngPTData.Rows.Count
y = rngPTData.Columns.Count

Set rngPTTot = rngPTData.Cells(1).Offset(x - 1, y - 1)
'MsgBox rngPTTot.Value
Range(rngPTTot).Select
Selection.ShowDetail = True
End With


End Sub

any ideas how to select the variable (the cell, not the value) within a range, or am I on the wrong track?

thanks

Doug

david000
10-13-2008, 08:58 AM
rngPTTot.ShowDetail = True

dragon576
10-13-2008, 09:16 AM
Managed a work around that resolved issue

Sheets("Test").Select
Sheets("Test").Range("C65536").End(xlUp).Select
Selection.ShowDetail = True

Not sure if it is the most efficient method, but it appears to work.
Thanks

anyway

Doug

Ryder
01-10-2009, 01:11 PM
Just a suggestion: In your code, the range object "rngPTTot" is the cell containg the grand total of the pivot table. That is what the Set statement did for you, so to extract the value in that cell, use rntPTTot.Value just like in the MsgBox statement.

You can stash it in a variable and do what you like with it

NewVariable = rngPTTot.Value

or pop it right into another cell address like B6 with

Range("B6").Value = rngPTTot.Value

BTW - I was stuck on getting the Grand Total, and your approach turned on the lightbulb for me... so thanks!