How to copy a sheet and keep all its properties

New Contributor

Hello dear all,


I made an EXCEL sheet (named Sheet1) in which I fill up a table manually. This table is the data source of several pivot tables and charts, also located in Sheet1.

I would like to make several copies of this sheet (one per week), so I just right-clicked on it and made a copy that I called Sheet2.


However I noticed that data source of the Sheet2's pivot tables and charts is the table of Sheet1, not the table of Sheet2. It means that filling up the table of Sheet2 won't actualize the pivot tables and charts of Sheet2, but modifying the table of Sheet1 will actualize Sheet2's pivot tables and charts.


Is there a way to make a copy of an EXCEL sheet and have all the datasources automatically located in this new sheet ?


Thanks a lot in advance. 



2 Replies

Dear @Hans Vogelaar 


Thanks a lot for your anwer. Unfortunately the VBA macro describes in this post is obsolete, it is not working. However I will keep looking in that direction.