Chart Series name referencing cell address Excel 2010 VBA

Question

I am using Excel 2010.

I have a XY scatter chart built and formatted the way I like. I reuse this chart many times, so I just copy and paste the chart into a new worksheet containing the new data that I want to plot. After pasting the chart in the new worksheet, I use the following macro to update the chart with the new data:

Sub DasyLabOilFDa()

Dim SeriesName As Range
Dim FirstSeriesValues As Range
Dim ElapsedTime As Range

'find cell addresses of elapsed time column
Range("C1").Select 'pick cell above the elapsed time column
Selection.End(xlDown).Select 'pick the elapsed time column header
ActiveCell.Offset(1, 0).Select 'selects first data value in Elapsed Time column
Set ElapsedTime = Range(Selection, Selection.End(xlDown)) 'set ElapsedTime variable to the range of data

'find cell addresses for FirstSeries in the top chart
Range("D1").Select 'pick cell above the first series column
Selection.End(xlDown).Select 'pick the first series column header
Set SeriesName = ActiveCell 'set SeriesName variable to the name of the data column's header
ActiveCell.Offset(1, 0).Select 'selects first data value in data column
Set FirstSeriesValues = Range(Selection, Selection.End(xlDown)) 'set FirstSeriesValues variable to the range of data

ActiveSheet.ChartObjects("TopFDa").Select
ActiveChart.SeriesCollection(1).Name = SeriesName
ActiveChart.SeriesCollection(1).Values = FirstSeriesValues
ActiveChart.SeriesCollection(1).XValues = ElapsedTime

End Sub

There are more than one data series I am plotting but the above is enough code to show how I am getting the macro to populate the chart I pasted in the new worksheet with the worksheet's data.

After the macro runs the chart will be correctly naming the series' name as seen in the legend (the series' name was determined by the data column's header)

The issue is that the series name is not referencing the cell address. When I look to edit the series, the Series name input box is blank.

How can I change my code so that the resulting chart is referencing the cell address of the column header as the Series name?

Solution

I checked the Excel Developer Reference in my copy of Excel 2007 and in the documentation for the Series.Name Property it says this:

Remarks

You can reference using R1C1 notation, for example, "=Sheet1!R1C1".

I've tested this and it works for me: I set it to a particular cell, and when I changed the text in that cell, the series title updated automatically.

In your case you should use

ActiveChart.SeriesCollection(1).Name = "='" & ActiveSheet.Name & "'!" & SeriesName.Address(,,xlR1C1)

Comments

[Jon Peltier]

Since Excel 2007, you can use either xlA1 or xlR1C1 notation. I usually use: Series.Name = TheRange.Address(, , , True) where True in the fourth argument supplies the prefix with the properly punctuated worksheet name.

[Jon Peltier]

The series name was not empty, it was correct in the legend. It was however not linked to the cell referenced in the code.