Naming a chart with a cell value from the active sheet

Question

I've got several sheets in a workbook. Each sheet contains test results with identical formatting. I would like to create a macro that creates a chart in each sheet that plots data from only that sheet. I've gotten this far:

Sub Macro()
    Dim sSheetName As String
    sSheetName = ActiveSheet.Name
    Sheets(sSheetName).Select
    Range("G7:I5241").Select 'range to plot
    Range("K7").Select 'location of plot
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmoothNoMarkers).Select 'select plot type
    ActiveChart.SeriesCollection.NewSeries   
    ActiveChart.FullSeriesCollection(1).Name = "='sSheetName'!R1C2" 'attempting to name the plot by a cell value in the active sheet

However, this last statement is not successful at creating a link to cell R1C2 in the active sheet.

Screenshot:

Any ideas?

Answer 1

You're using a string literal in the name assignment:

ActiveChart.FullSeriesCollection(1).Name = "='sSheetName'!R1C2" 

Unless you have a worksheet "sSheetName", this will fail, expectedly. Do this instead:

ActiveChart.FullSeriesCollection(1).Name = "='" & sSheetName & "'!R1C2" 

This uses the value you've previously assigned to your variable sSheetName