Internal Named range conflict

[stepone]

Hi,

I have a Report Template spread-sheet which uses some Excel VBA code to change the data range of a chart, and then copy the worksheet containing the chart to a new, blank, workbook.

The code which updates the data range is as follows ;

ActiveChart.SeriesCollection(1).Formula = _
       "=SERIES(,'Part 1'!R" & lRow & "C3:R" & lRow + lCount - 1 & "C3,'Part 1'!R" & lRow & "C4:R" & lRow + lCount - 1 & "C4,1)"

The 'lRow' value is always 25, and the 'lCount' value varies depending on how many data rows there are, but, for example, if there are 5 rows the above code will translate to ;

     ActiveChart.SeriesCollection(1).Formula = _
       "=SERIES(,'Part 1'!R25C3:R29C3,'Part 1'!R25C4:R29C4,1)"

The worksheet containing this chart is then copied to a new workbook as follows ;

Sheets(strWorksheet).Move Before:=Workbooks(strJobFolder).Sheets(1)

At which point I get an error message ;

A formula you want to move or copy contains the name R25C3 which conflicts with a valid range reference or a name used internally by Excel 2013, and must be modified.

There is no Name in the Name Manager called R25C3. It seems to be to be caused by the setting of of the Formula of the chart series.

Is it possible to use 'A1B1' type notation to set the series data values? I can't see any example of this in the documentation. Or is there some other way of removing the hidden 'name' reference before copying to a new workbook ?

Thanks very much,
StepOne

 

[Rx_]

Sheets(strWorksheet).Move Before:=Workbooks(strJobFolder).Sheets(1)
Sub HeyJoe()
ActiveWorkbook.Sheets.Add(, ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)).Name = "joe"
End Sub
The Before:= statement can be tricky. It might be looking for a fully qualified path.
Your right, some of the error descriptions can be way off.
Just a guess, but the Before or After can require a full object qualification.