Pivot table data source remains fixed when copying sheets

[tfmeier]

I have workbook with sheet Rev-0. This sheet contains a table (cols A-G) and a pivot table (col. J) accessing the table in cols A-G. The table is named (Rev_0).

I's possible that a copy needs to be made of this sheet (new sheet called Rev-1) using the standard right-click | Move or Copy command. This creates the Rev-1 as expected. However when I check the data source for the pivot table in sheet Rev-1 the data source still points to Rev-0 where I need it to access the table in Rev-1. The table in Rev-1 has a new name as a result of the copy function.

While I can easily change the data source, I create this for a client where this kind of modification is too much to ask. Is there a way to make the pivot table data source follow the sheet; i.e. pivot table in sheet Rev-1 accesses the table in Rev-1?

[#2]

Change default setting of Pvt by uncheck "source data with file" from then while moving the sheet pvt will pick new sheet data source. @tfmeier 

[#3]

Ok, this is what I've done:

• Changed the setting 'save source data with file' in the pivot table in sheet Rev-0
• Made a copy of Rev-0 > Rev-1
• Checked the data source of the pivot in Rev-1; it still points to the table in Rev-0

I'm using the latest version of Excel

@laizer1105 

[#4]

I'm running into this issue right now, did you ever find a fix?

[#5]

This seems to have never been resolved here. My issue is even slightly weirder.  In my original sheet, I gave the source data range a new name. Now, when I copy that sheet the PT on the new sheet refers to the OLD name of the data range on the original sheet! Blows my mind how this all gets retained in the background. I am trying to refresh caches, etc, but have not found a solution yet.  Any thoughts out there?

[#6]

Best to ignore my previous contribution; turns out the source table of the pivot table had been "hard coded" in VBA.  Was easily fixed in the end.  I'd be happy to provide the related VBA code on request.