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
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