Pivot table data source remains fixed when copying sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-2072932%22%20slang%3D%22en-US%22%3EPivot%20table%20data%20source%20remains%20fixed%20when%20copying%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2072932%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20workbook%20with%20sheet%20Rev-0.%20This%20sheet%20contains%20a%20table%20(cols%20A-G)%20and%20a%20pivot%20table%20(col.%20J)%20accessing%20the%20table%20in%20cols%20A-G.%20The%20table%20is%20named%20(Rev_0).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI's%20possible%20that%20a%20copy%20needs%20to%20be%20made%20of%20this%20sheet%20(new%20sheet%20called%20Rev-1)%20using%20the%20standard%20right-click%20%7C%20Move%20or%20Copy%20command.%20This%20creates%20the%20Rev-1%20as%20expected.%20However%20when%20I%20check%20the%20data%20source%20for%20the%20pivot%20table%20in%20sheet%20Rev-1%20the%20data%20source%20still%20points%20to%20Rev-0%20where%20I%20need%20it%20to%20access%20the%20table%20in%20Rev-1.%20The%20table%20in%20Rev-1%20has%20a%20new%20name%20as%20a%20result%20of%20the%20copy%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhile%20I%20can%20easily%20change%20the%20data%20source%2C%20I%20create%20this%20for%20a%20client%20where%20this%20kind%20of%20modification%20is%20too%20much%20to%20ask.%20Is%20there%20a%20way%20to%20make%20the%20pivot%20table%20data%20source%20follow%20the%20sheet%3B%20i.e.%20pivot%20table%20in%20sheet%20Rev-1%20accesses%20the%20table%20in%20Rev-1%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2072932%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2073008%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20data%20source%20remains%20fixed%20when%20copying%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2073008%22%20slang%3D%22en-US%22%3E%3CP%3EChange%20default%20setting%20of%20Pvt%20by%20uncheck%20%22source%20data%20with%20file%22%20from%20then%20while%20moving%20the%20sheet%20pvt%20will%20pick%20new%20sheet%20data%20source.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F831234%22%20target%3D%22_blank%22%3E%40tfmeier%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2076848%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20data%20source%20remains%20fixed%20when%20copying%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2076848%22%20slang%3D%22en-US%22%3E%3CP%3EOk%2C%20this%20is%20what%20I've%20done%3A%3C%2FP%3E%3CUL%3E%3CLI%3EChanged%20the%20setting%20'save%20source%20data%20with%20file'%20in%20the%20pivot%20table%20in%20sheet%20Rev-0%3C%2FLI%3E%3CLI%3EMade%20a%20copy%20of%20Rev-0%20%26gt%3B%20Rev-1%3C%2FLI%3E%3CLI%3EChecked%20the%20data%20source%20of%20the%20pivot%20in%20Rev-1%3B%20it%20still%20points%20to%20the%20table%20in%20Rev-0%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI'm%20using%20the%20latest%20version%20of%20Excel%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F892982%22%20target%3D%22_blank%22%3E%40laizer1105%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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?

3 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

@laizer1105 

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