How to refer to an existing Worksheet Name

[Peter1961]

Dear All,

I wish to compare different stock instrument (Instrument1, Instrument2, Instrument3 and so on). Each Instrument has several types of data (Close, MA1, MA2, MA3 and so on).

I created specific names for the Instruments with formulas as:

Instrument1Close:
=OFFSET(Instrumet1Data!$M$103; Instrumet1Data!$I$6;0; Instrumet1Data!$I$5;1)

Instrument1MA1:
=OFFSET(Instrumet1Data!$N$103; Instrumet1Data!$I$6;0; Instrumet1Data!$I$5;1)

Instrument2Close:
=OFFSET(Instrumet1Data!$O$103; Instrumet1Data!$I$6;0; Instrumet1Data!$I$5;1)

Instrument2MA1:
=OFFSET(Instrumet1Data!$P$103; Instrumet1Data!$I$6;0; Instrumet1Data!$I$5;1)

And so on.

I want to see them on a chart. The code for the chart is the following:
The following DO NOT WORK:

.XValues = ThisWorkbook.Names("Date").RefersToRange          
.Values = ThisWorkbook.Names(InstrumentName & "Close").RefersToRange                                                          
.XValues = ThisWorkbook.Names("Date").RefersToRange          
.Values = ThisWorkbook.Names(InstrumentName & "MA1").RefersToRange

The following work:

.XValues = "='" & ThisWorkbook.Name & "'!Date"
.Values = "='" & ThisWorkbook.Name & "'!Instrument1Close"

ALSO, The following DO NOT WORK:

Y1AxisMax = Application.WorksheetFunction.Max(ThisWorkbook.Names(InstrumentName & "Close").RefersToRange)
Y1AxisMin = Application.WorksheetFunction.Min(ThisWorkbook.Names(InstrumentNamee & "Close").RefersToRange)

'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn
' CREATE A CHART AND DELETE SERIE
'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn
i = 1
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Compare'!$A$103:$B$150")
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveSheet.ChartObjects(i).Name = "Chart" & (i)
 
ActiveSheet.ChartObjects("Chart" & i).Activate
With ActiveChart
    .SeriesCollection(1).Delete
End With
j = 1
'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn
'ADD SERIES TO THE CHART
'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn
InstrumentName= Cells(InstrumentNameRow, InstrumentNameColumn).Value
For i = 1 To InstrumentNumber
    'ActiveSheet.ChartObjects("Chart" & i).Activate
        With ActiveChart
            'j = 1
            .SeriesCollection.NewSeries
            With .SeriesCollection(j)
                .Name = InstrumentName & "Close"
                ‘.XValues = "='" & ThisWorkbook.Name & "'!Date"    ‘This WORK
                ‘.Values = "='" & ThisWorkbook.Name & "'!Instrument1Close"        ‘This WORK
              
                .XValues = ThisWorkbook.Names("Date").RefersToRange    ‘Don’t WORK  
                .Values = ThisWorkbook.Names(InstrumentName & "Close").RefersToRange  ‘Don’t WORK
            End With
            
            j = j + 1
            .SeriesCollection.NewSeries
            With .SeriesCollection(j)
                .Name = InstrumentumNeve & "CloseSzlkban"
                .XValues = ThisWorkbook.Names("Date").RefersToRange
                .Values = ThisWorkbook.Names(InstrumentName & "MA1").RefersToRange
            End With
            'Stop
        End With
        j = j + 1
        k = k + 1
        InstrumentNameRow = InstrumentNameRow + 1
        InstrumentName= Cells(InstrumentNameRow, InstrumentNameColumn).Value
 
Next i

Thank you very much in advance for your help!

Peter