Pivot table data source remains fixed when copying sheets

Occasional Contributor

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 Replies

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 

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