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