Copy a chart from one sheet to another using the target sheet data for the chart in Excel

Question

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.

Answer 1

I've used Jon Peltier's Change Series Formula a lot (it is actually linked in the middle of the page provided in the answer by GGuess). It is an add-in that can be obtained here.

It is extremely useful, and it probably covers the needs of most cases of this type. It provides a convenient interface (UserForm) to search and replace strings in chart series formulas. It is actually more versatile than a "change source worksheets", since one can use search and replace for changing other parts of series formulas for many series at once.

Answer 2

The easiest way to copy a chart to another sheet and have the chart link to data on the new sheet, isn't to copy the chart. The easiest way is to copy the sheet including the chart, then change the data on the copied sheet.

The second-easiest way, if the chart's data is organized simply, is to use Select Data from the ribbon or the right-click menu, and change the range indicated in the Chart Data Range RefEdit at the top of the dialog.

A tedious way is to change all of the sheet references in all of the chart's series formulas, for example, changing all instances of Sheet1 in the following formula to Sheet2: =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$4,Sheet1!$B$2:$B$4,1)

As @sancho.s points out, you can also do this with VBA using code posted in my Change Series Formula tutorial. These algorithms are built into my commercial Excel add-in software.