How to copy a sheet and keep all its properties

[Pierrick_Guinot]

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. 

Regards,

[#2]

@Pierrick_Guinot 

You might run the macro listed in Copy worksheet with embedded pivot tables and have pvito tables point to data in new tab not origina... 

[#3]

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.

Regards,