Forum Discussion
Pivot table data source remains fixed when copying sheets
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?
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
- I'm running into this issue right now, did you ever find a fix?
