Drill into a Pivot Table using a Macro

[brennaboy]

Hi,

As part of a macro I am putting together for the automation of a task, I need to tell the macro to drill into the cell of a pivot table and then copy the data to another tab/spreadsheet.

The cell reference could potentially change from one day to the next, so I was going to use the Index function to try and avoid this.

The cell I wish to drill into is at the intersection of "NOTE" and "Grand Total". Below is the function I will use.

However - I am not sure how to put this into the macro code to tell the macro to drill into this cell - can anyone help?

=INDEX(B$11:M$285,MATCH("NOTE",$D$11:$D$285,0),MATCH("Grand Total",$B$11:$M$11,0))

Many thanks,

B.

[CatDaddy]

[vba]Private Sub Get_GTotalPT()
Dim rngPTTot As Range, rngPTData As Range
Dim x As Long, y As Integer

With ActiveSheet
Set rngPTData = .PivotTables(1).DataBodyRange

'THE NEXT PART FOUND THE INTERSECTION OF LAST ROW/COL
x = rngPTData.Rows.count
y = rngPTData.Columns.count

Set rngPTTot = rngPTData.Cells(1).Offset(x - 1, y - 1)
rngPTTot.ShowDetail = True
End With

End Sub[/vba]

if you have static row numbers you can change x/y to x = rngPTData.Rows(num), if they are static you also will not need the offset