select pivot table range
 

I use the code below to find the Total cell in a pivot table.
Issue:
When a cell in the range is active; Selection.End(xlDown).Select, sometimes
selects the active cell, and sometimes selects the Total cell at the bottom
of the range which is correct.

How do I force the select to the bottom of the Pivot Table?

Range("A19").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Selection.ShowDetail = True

select pivot table range
 

Ah! Great question! It took me a while to figure out how to do this. Now,
I can share the answer with you.

Recorded Macro with Absolute Reference: Sheet1!R1C1:R30C10
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sheet1!R1C1:R30C10").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

Recorded Macro with Absolute Reference changed to Relative Reference:
Sheets("Sheet1").Range("A1").CurrentRegion).
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
Sheets("Sheet1").Range("A1").CurrentRegion).Create PivotTable _
TableDestination:=Sheets("Sheet2").Range("A3"),
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10


The second example will extend the Pivot Table range to the entire Pivot
Table, no matter whether the Pivot Table source data grows or shrinks over
time.


Regards,
Ryan---

--
RyGuy


"wynnyderpooh" wrote:

I use the code below to find the Total cell in a pivot table.
Issue:
When a cell in the range is active; Selection.End(xlDown).Select, sometimes
selects the active cell, and sometimes selects the Total cell at the bottom
of the range which is correct.

How do I force the select to the bottom of the Pivot Table?

Range("A19").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Selection.ShowDetail = True