Replicate Graphs easily with different data sources

[owen1r]

Hi Guys,

I've been having issues with charts on Excel 2016

I have 5 different worksheets with calculations on them, each sheet has cells in identical positions, the only difference being the input values for the calculations.

I need 8 graphs per worksheet (5x8 is a lot of graphs!)

I've already produced the graphs, but the size/positions of the charts are slightly different and doesn't look very professional when viewed side by side. So I'm wanting to copy the format across all charts.

My issue is, I've tried using the chart template tool, but when it applies it completely stuffs up the chart. I get a similar issue when I Copy -> Paste Special -> Format.

I'd rather not tediously copy and paste the original chart and change the data sources manually, as each chart has approx 20 different series.

Is there an easy way (I assume VBA) to change all of the data series values easily? Or perhaps a solution to getting custom chart templates to work as intended?

Thanks in advance.

Owen

 

[Jon Peltier]

If the data is laid out exactly the same on each sheet, it's fairly easy to do what you want. I describe it in my tutorial Copy Chart to New Sheet and Link to Data on New Sheet.

Say you have data and charts on Sheet1, and you have duplicate data on Sheet2 which you want to chart.

Make a copy of Sheet1, which is now called Sheet1 (2). The charts on Sheet1 (2) link to the data on Sheet1 (2); they look the same as the charts on Sheet1, because the data is the same. Copy the data from Sheet2 and paste it on Sheet1 (2). Now the charts reflect the new (pasted) data. Get rid of Sheet2, and rename Sheet1 (2) to Sheet2.

Rinse and repeat.

 

[owen1r]

Thanks Jon,

I should have figured that one out myself.

Works perfectly!