VBA Select Pivot Table

Question

I am trying to select a pivot table using VBA, and have encountered an issue in that the pivot table may start at A3 (if there are no report filters activated) or A6 (if 3 report filters have been chosen) etc. Accordingly the pivot table will not always be on the same point on the worksheet. Clearing the pivot only removes all the data and report filters, although does not move the pivot table back up to its original position. Is there any way to use VBA to select the pivot table (so as to be able to copy and paste the data into a new worksheet) regardless of where it may be positioned on the worksheet?

Thanks.

UPDATE - I have since figured out how to do this. Its quite simple really:

Dim PT As PivotTable
Set PT = ActiveSheet.PivotTables(1)
PT.TableRange1.Select

You should write it out as an answer instead of an edit, and confirm it. — Mike Weir
Thanks - I will do, as soon as the time limit for answering my own questions expires! — Nat Aes
Now that you have a handle on how to select the PivotTable, you may very well want to know how to reference the different parts of a PivotTable. Samples can be found here: peltiertech.com/WordPress/referencing-pivot-table-ranges-in-vba — tbur

Nat Aes Nat Aes · Accepted Answer · 2013-08-23T09:04:39

Use the following code (given for a pivot table names PivotTable1):

Dim PT As PivotTable
Set PT = ActiveSheet.PivotTables(1)
PT.TableRange1.Select

VBA Select Pivot Table

1 Answers