Loop that add named range as seriescollection to my chart VBA

[espenskeie]

Hi

I have a loop that I want to run to add different series to my chart... But I cannot find the right solution... Here is what I have come up with so far:

Sub NameMan()


    Dim wb As Workbook
    Dim ws As Worksheet, wsY As Worksheet
    Dim lr As Long, sht As Long
    Dim getdate As Date, todate As Date
    Dim shtName As String
    Dim Chart2 As ChartObject
    Dim wsX As Worksheet
    
       Set wb = ThisWorkbook
       Set ws = wb.Sheets("Graphics")
      
    For Each wsX In ThisWorkbook.Worksheets
    If wsX.Name <> "Graphics" Then
    k = k + 1
    
    shtName = wsX.Name
    
    Sheets(shtName).Activate
    
        With ActiveSheet
        lr = .Range("A2").End(xlDown).Row
        
        getdate = Sheets("Graphics").Range("P3").Value
        Res1 = Application.Match(CLng(getdate), .Range("A2:A" & lr), 0)
        
        todate = Sheets("Graphics").Range("P4").Value
        Res2 = Application.Match(CLng(todate), .Range("A2:A" & lr), 0)


        ws.Names.Add Name:="" & shtName & "", RefersToR1C1:="='" & shtName & "'!R" & Res1 + 1 & "C2:R" & Res2 + 1 & "C2"
        ws.Names("" & shtName & "").Comment = ""
        End With
        
    ws.ChartObjects("Chart 2").Activate
    ActiveChart.SeriesCollection(k).XValues = "='" & shtName & "'!A" & Res1 + 1 & ":A" & Res2 + 1 & ""
    ActiveChart.SeriesCollection(k).Values = ws.Range(shtName)


    End If
    Next
    ws.Activate
End Sub

It stops at this line:

ActiveChart.SeriesCollection(k).XValues = "='" & shtName & "'!A" & Res1 + 1 & ":A" & Res2 + 1 & ""

Kind regards
Espen

 

[espenskeie]

Ok, all of a sudden the solution showed up:

ActiveChart.SeriesCollection.NewSeries

I was missing that line in here:

ws.ChartObjects("Chart 2").Activate
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(k).XValues = "='" & shtName & "'!A" & Res1 + 1 & ":A" & Res2 + 1 & ""
    ActiveChart.SeriesCollection(k).Values = Range(shtName)