Selecting an active worksheet AND an active table that keep changing names!

In a project I am working on I import a sheet with a pivot table from another workbook.

I then select a "Grand Total" cell and "explode" it, which creates a new sheet with all the raw data on it.

I need to work with that.

At first from a recorded macro it was:

Please Login or Register  to view this content.

And I now have tweaked it to:

Please Login or Register  to view this content.

which got me past 1) The fact that every time I import a revised pivot table in and expand it, "Sheet1" gets a brand new incremental "Sheet<x>" name given to it ("Sheet1", "Sheet2","Sheet3",...) and got me past the first Table2 reference that was in there, but I believe that second one - the one that is in Range("Table2[PersonName]") is causing me to crash with:

Run-time error '1004':
Method 'Range' of object '_Global' failed

I am creating by trial and error, so please do not presume I know what I am doing!

It will be followed by:

Please Login or Register  to view this content.

So if you see anywhere where I might run into another train wreck, please warn me!

Why instead of a table you just use ranges? That would be a good idea. So instead of :

Please Login or Register  to view this content.

Something like this

Please Login or Register  to view this content.

I cannot help you more because I canno see your sheet. But try this. The code asumes your data is in column "B" and starts in row 1.

Please Login or Register  to view this content.

Thanks