Macro - Dynamic cell selection
 

I have a dynamic range of data that I pivot (multiple consolidation range)
using a macro.

the next step is to double click on the intersection of grand totals - this
allows me to get the desired lay out.

Problem: As my range is dynamic this "cell" isn't constant. I need a macro
that automatically pics up the bottom right corner of a pivot table.
Here is the layout of the pivot table - the cell in question is the one with
"13" in it.


Count of Value Column
Row basic commission overtime Grand Total
1 1 1 1 3
2 1 1 2
3 1 1 2
4 1 1
5 1 1 2
6 1 1 1 3
Grand Total 6 3 4 13

Macro - Dynamic cell selection
 

Chris,

Change the A5 to be the cell where you are placing the pivot table

Dim myR As Range
Set myR = Range("A5").CurrentRegion
Set myR = myR(myR.Cells.Count)
MsgBox myR.Address & " is the last cell of the pivot table."

HTH,
Bernie
MS Excel MVP


"Chris" wrote in message
...
I have a dynamic range of data that I pivot (multiple consolidation range)
using a macro.

the next step is to double click on the intersection of grand totals - this
allows me to get the desired lay out.

Problem: As my range is dynamic this "cell" isn't constant. I need a macro
that automatically pics up the bottom right corner of a pivot table.
Here is the layout of the pivot table - the cell in question is the one with
"13" in it.


Count of Value Column
Row basic commission overtime Grand Total
1 1 1 1 3
2 1 1 2
3 1 1 2
4 1 1
5 1 1 2
6 1 1 1 3
Grand Total 6 3 4 13