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.