Retain Chart Formatting when change series references

[brianhfield]

Hello! I use named ranges in charts and VBA to change the named ranges so that I can update hundreds of charts in an automated fashion. Let's assume a have a chart that has series1 named range1 and series2 named range2 and i want to use vba to change this so that series1 references named range range3 and series2 references named range range4. I can do all of this successfully.
However, if I copy the chart from sheet1 and paste it into sheet2, and then I execute a vba script that replaces the series references, the chart formatting reverts to the default formatting and I lost the formatting from the original set of charts. Does anyone know how I can prevent excel from resetting the chart formatting when the series names are changed?

 

[myall_blues]

I'd suggest you try the following.

1. Create your 'formatted' graph and right click on it.
2. Choose Save as Template... and save it with a name of your choice.
3. Copy the graph to Sheet2 and run your vba script.
4 Now start the macro recorder and record the following: On the Insert menu->Charts Group click on Recommended Charts->All Charts tab->Templates and select your template.
5. The template will be applied to your new graph.
6. Stop the macro recorder and add the recorded code to your existing code that changes the references.

 

[Jon Peltier]

This is because of the mysterious "Properties Follow Chart Data Point" setting (click the link to read my tutorial). Because of this setting, most customized formatting n a chart is blown away when you modify the data range for some or all series in the chart. You can go to File > Options > Advanced, scroll down to Chart, and uncheck "Properties Follow Chart Data Point for Current Workbook". Then change your data and the formatting is preserved. I always go back and check it in case I want something the setting provides (though it's hard to know what that might be).

You can turn off this setting in your code with this line:

ActiveWorkbook.ChartDataPointTrack = False

and then later change it back to True.