VBA Excel - reference variably named worksheets for chart series

Question

I have a VBA code that runs over a series of files. It creates a second worksheet in a given workbook, and then gets the data from the first worksheet in that workbook. This worked well when the first worksheet in every file was named "Fall". Now, however, the first worksheet in each file has a different name for each workbook.

I need to create the second worksheet and then get the data from the first worksheet in each file without explicitly using the worksheet's name. One line in the previous code looked like this:

ActiveChart.FullSeriesCollection(1).Name = "=Fall!$A$2:$A$2"

This gave me the data series name on the chart. But now the WS name is NOT "Fall". It changes from file to file. It is also the name of the file itself, if that helps.

I just need to reference the previous WS -not the "active" sheet where the chart is being built- and I don't know what to call it. I have tried:

 ActiveChart.FullSeriesCollection(1).Name = "='" & Sheetname.Name & "' & !$A$2:$A$2"

This is choosing the correct cell and when I hover over the code in Debug, the correct name is showing up, but when I run the code I get "Runtime error '1004: Parameter not valid" and it won't continue to run. That is the line giving me trouble.

I tried countless variations - removing the single quote, adding quotes, removing ampersands, changing to Sheet1.name rather than Sheetname.Name, changing to Sheet(1), or Worksheet(1). I tried recording a macro, but that gave me the specific reference to the page in that particular file, so it didn't help. I also tried changing the file from a .csv to a .xlsm; didn't help.

I tried changing !$A$2:$A$2 to just !$A$2. I don't know what the problem is. I feel like it is probably something simple.

This is my sample of code:

Sub Graph_NEW()
' This is the macro that will graph a "Historic" line (flat mean) with the projected data for ea HUC8.
' Graphs columns B & C then adds G, to a second worksheet

    Dim Sheetname As Worksheet
    Set Sheetname = Worksheets(1)
        
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Shapes.AddChart2(227, xlLine).Select
    ActiveChart.FullSeriesCollection(1).Name = "='" & Sheetname.Name & "'!$A$2:$A$2"
    ActiveChart.FullSeriesCollection(1).Values = "'" & Sheetname.Name & "'!$C$2:$C$140"
    ActiveChart.FullSeriesCollection(1).XValues = "'" & Sheetname.Name & "'!$B$2:$B$140"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "=""Historic Mean"""
    ActiveChart.FullSeriesCollection(2).Values = "='" & Sheetname.Name & "' & "!$F$2:$F$140"

    ActiveChart.FullSeriesCollection(1) = Sheets("Fall").Cells(2, 1).Value

    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "YEAR"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "YEAR"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 4).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With

I have also highlighted line 9 of the code because this line is formatted differently and I need help to know how I'd have to adjust it as well. Currently it's the old code.

This is a small sample of one .csv file:

HUC8    YEAR    RO_MM   HIST    HUC Historic Mean
mricgcm3_45Fall_1010001 1961    62.4    HistFall    1010001 141.7455    
mricgcm3_45Fall_1010001 1962    107.6   HistFall    1010001 141.7455    
mricgcm3_45Fall_1010001 1963    140.1   HistFall    1010001 141.7455    
mricgcm3_45Fall_1010001 1964    172.3   HistFall    1010001 141.7455    
mricgcm3_45Fall_1010001 1965    138.5   HistFall    1010001 141.7455    
mricgcm3_45Fall_1010001 1966    147     HistFall    1010001 141.7455    
mricgcm3_45Fall_1010001 1967    105.1   HistFall    1010001 141.7455    
mricgcm3_45Fall_1010001 1968    62.6    HistFall    1010001 141.7455    
mricgcm3_45Fall_1010001 1969    170.1   HistFall    1010001 141.7455    

Also, I have asked this question elsewhere. I don't know why that is frowned upon in some places, I'm just trying to get help. It was not answered anywhere, but I wanted to be upfront about that. Thanks guys.

Answer 1

1. You did not add a new series so there is 0 Series object in the collection for you to change the name. So you need to add a Series before you can modify its property.

2. Since you are referencing only 1 cell, you can trim it to:

"='" & Sheetname.Name & "'!$A$2"

3. It is highly recommended to avoid the use of Select/Activate, ActiveSheet and other similar reference as it is not reliable (it can break when you change the focus of the workbook/worksheet while the code is running).

Please read this on how to avoid using Select/Activate

Taking above into account, try this snippet and modify it to your full code:

Sub Graph_NEW()
' This is the macro that will graph a "Historic" line (flat mean) with the projected data for ea HUC8.
' Graphs columns B & C then adds G, to a second worksheet

    Dim dataSheet As Worksheet
    Set dataSheet = ActiveWorkbook.Worksheets(1)
    
    Dim newSheet As Worksheet
    
    
    Set newSheet = ActiveWorkbook.Worksheets.Add(After:=dataSheet)
    
    Dim newChart As Chart
    Set newChart = newSheet.Shapes.AddChart2(227, xlLine).Chart
    
    With newChart
        With .SeriesCollection.NewSeries
            .Name = "='" & dataSheet.Name & "'!$A$2"
            .Values = "='" & dataSheet.Name & "'!$C$2:$C$140"
            .XValues = "='" & dataSheet.Name & "'!$B$2:$B$140"
        End With
        
        With .SeriesCollection.NewSeries
            .Name = "Historic Mean"
            .Values = "='" & dataSheet.Name & "'!$F$2:$F$140"
        End With
    End With
End Sub