I have several worksheets which have different data but organized in the same way (same number of col and rows). I created several charts in sheet1 which I would like to copy to sheet2. while the normal copy/paste copies the charts to sheet2, the charts is still referring to data in sheet1, not in sheet2. How can I automatically make them use sheet2 data rather than sheet after copying?

As a work around, I tried copying sheet1 and called it sheet2 (which copies all data and charts), then copy and pasted the real sheet2 data in this new sheet. This works, but I was hoping there is a faster way and perhaps a macro that copies all charts from sheet1 to sheet2 and automatically updates the references.