Excel 2007 : Graph named series not copying with sheet

**sweep** · 12-02-2010, 09:26 AM

Afternoon all,

I have a worksheet that is intended to be duplicated in its workbook as many times as is required.

The worksheet contains a graph, for which the data is in dynamic named ranges specific to the worksheet.

When I make a copy of the worksheet, the sheet is successfully duplicated, as are the named ranges. However, the graph no longer refers to the named ranges, rather the static range that was defined by the dynamic range prior to copying.

Am I doing something wrong, or is there a workaround for this?

Thanks,

Dave

**Andy Pope** · 12-02-2010, 09:50 AM

That's just how it works. The named ranges are replaced with references.

You will need to rebuild the named ranges in the newly copied sheet.

This quick and dirty code will produce a copy sheet and update the formula.

Please Login or Register  to view this content.

**sweep** · 12-02-2010, 09:59 AM

Thanks Andy,

I'd come up with much longer code than that!

Cheers,

Dave

**mc36155** · 02-11-2013, 07:24 AM

Hello

im trying to get the code that was posted above to work with the chart in the attached workbook. hoping that to copy the sheet and maintain the chart dynamic references you can just click the "copy sheet" button.Cyanidation Template - 2012.xlsm

**preddeid** · 08-07-2014, 08:02 AM

I have the same problem as the person that posted the thread: I have a chart with series values that are defined names. When you copy the worksheet to a new worksheet the series values are turned into cell references instead of referencing the new worksheet and the defined names.

I copied the above code and have tried to run it, but it stops at the line:

objSeries.Formula = Replace(chtMaster.SeriesCollection(lngIndex).Formula, "Master", "'" & objCopy.Name & "'")

with 'Run-time error 1004: Application-defined or object-defined error'.

I am still learning VBA, and I can't see what is wrong. All of the variable values look OK to me but I'm must be missing something. I am using Excel 2013, could that be an issue?

I have more than 100 charts that need to be copied, plus more on an ongoing basis. I would hate to have to do them manually. Can you please help?

I would greatly appreciate any advice.