Set a Chart series source to a named range via Macro

Question

I have a sheet with a existing chart and the proper dynamic named ranges to feed that chart the right data.

My problem comes that the sheet is a template that is copied and the copies' charts don't point to the named ranges anymore. The named ranges get copied to the sheet (the ranges are sheet specific) so it seems like it should be a matter of just updating the series source data. Good old record gave me the following syntax when I recorded doing that manually:

ActiveChart.SeriesCollection(1).XValues = "=='Risk 1'!PDates"
ActiveChart.SeriesCollection(1).Values = "=='Risk 1'!Plan"
ActiveChart.SeriesCollection(2).XValues = "=='Risk 1'!ADates"
ActiveChart.SeriesCollection(2).Values = "=='Risk 1'!Actuals"

I've attempted to reset them to the named ranges with a modified version of that:

For Each Cht In TempSheet.ChartObjects
    'Series 1 is the Plan
    Cht.Activate 
    ActiveChart.SeriesCollection(1).XValues = "=='" & TempSheet.Name & "'!PDates"
    ActiveChart.SeriesCollection(1).Values = "=='" & TempSheet.Name & "'!Plan"

    'Series 2 is the Actuals
    ActiveChart.SeriesCollection(2).XValues = "=='" & TempSheet.Name & "'!ADates"
    ActiveChart.SeriesCollection(2).Values = "=='" & TempSheet.Name & "'!Actuals"
Next

I hit an error, "Application-defined or object-defined error", at the statement that tries to update the X Values for the first series.

I've also tried adding the PlotArea select statement in, just in case that was the issue, but that didn't fix the problem. I checked that the statements the collections are supposed to be getting set to evaluate correctly (ex. "=='" & TempSheet.Name & "'!PDates" evaluates to "=='Risk 1'!PDates", which is what the series should be pointed at).

With that, I'm fairly well stumped and would appreciate any help anyone could provide. Thanks in advance!

Is the problem that you have two equals signs in a row at the beginning of the formula? Other than that, it should work. — Jon Peltier, Mar 23 '15 at 20:48

score 0 · Answer 1 · answered Aug 20 '14 at 19:06

0

Rather than using a string that tries to be a range, try a real range and then get its values:

ActiveChart.SeriesCollection(1).XValues = TempSheet.Range("PDates").Value

(etc)

answered Aug 20 '14 at 19:06

James

20,957
5
26
41

Gave this a shot, I didn't get any errors, but when I checked the series' source manually it had been set to "={#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A}". – JMichael Aug 20 '14 at 20:33
1

When I dropped the ".Value" from the end of each statement the series ended up set to the range the named range covers, which is close, but I'm really looking for it to be set to the named range (so as to avoid having to update the chart range when the source range gets more rows added) – JMichael Aug 20 '14 at 20:42
As a note, I've implemented the code so that when a new row is added to the chart's source range, the macro that does that addition updates the chart to include the new row. Not ideal, but it gets the job done. – JMichael Aug 25 '14 at 19:24

score 0 · Answer 2 · answered Mar 23 '15 at 20:58

If the "template" worksheet has the data and chart the way you want, but with dummy data, save it as an official Excel template. Then when you need one of these sheets, right click on a sheet tab and click Insert... Select the template in the dialog and click OK. When this sheet is inserted based on the template, its chart will be linked to the Names in the sheet.

Set a Chart series source to a named range via Macro

2 Answers2

Linked