VBA - Select Cells from Pivot Table

Question

I am quite a novice at VBA and first time posting, so please bear with me.

I am trying to copy data from a pivot table and paste value into a separate worksheet. I need to leave the macro as flexible as possible to cater for any potential future changes to the pivot. In essence I would like to copy (for example) A6:E77, but this row range may need to change (so to A6:E84, rather than A6:G77) if the underlying data changes. I can select all the vertical non-blank cells in a discontinguous data set using:

    Range("A6", Range("A1048576").End(xlUp)).Select

However how can I then select all the cells in columns B:E that are alongside the cells chosen in column A?

I have tried using the following code, but it seems to ignore the End.(xlUp) command.

    Range("A6:E1048576", Range("A6:E1048576").End(xlUp)).Select

Any ideas? I appreciate the help :)

Answer 1

You can access pivot tables through their PivotTable objects in the PivotTables collection on the relevant Worksheet object.

For example a pivot table on your first sheet named PivotTable1 could be accessed as Sheet1.PivotTables("PivotTable1").

These PivotTable objects have useful properties such as DataBodyRange, DataLabelRange, ColumnRange, RowRange, PageRange, etc. These ranges are dynamically updated to point to the relevant parts of the pivot table.

There are also properties such as RowFields, ColumnFields, and DataFields that allow you to inspect the current structure of the PivotTable.

Without knowing more about what parts of the pivot you are trying to copy I can't give more specific advice. But you can take a look at the documentation for the PivotTable object here.