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

[p45cal]

Attach your workbook with realistic data in.

[Paul_Hossler]

Peter -- we like to use CODE tags to format and set off macro code

I added them for you, but next time you can use the [#] icon to add CODE and \CODE tags and them just paste your macro between

[Peter1961]

Dear p45cal and Paul,

Thank you for your prompt answer.

My workbook is in Hungarian, so I had to rewrite it for an English version, and find how to attach it.

Unfortunately, I did not find how to attach it. I go to “Go Advanced”, choose and add my file, then I “upload” it, but appears nothing in the “Home” window, so I cannot drag it to the next window.

Can you help me?

I did not make any other post in any other forum. This is my first forum with my first post.

Many thanks again for your help!

Peter

[p45cal]

It might be too big?
Save as xlsb and try again.
Failing that upload to a file-sharing site (there are loads) and give the link to it (and permission of course).

[Paul_Hossler]

It's not always intuitive

These are inserted 'Inline' but for workbooks, etc. I usually just click [Done] when I have the file(s) uploaded

Capture.JPG

Capture2.JPG

Capture3.JPG

[Peter1961]

Dear p45cal,

You were right, the file was too big.

Thanks again.

Peter

[p45cal]

Instead of:

.XValues = "='" & ThisWorkbook.Name & "'!MyDate"
.Values = "='" & ThisWorkbook.Name & "'!DJIAClose"

you can try:

.XValues = Range("MyDate")
.Values = Range("DJIAClose")

and there'd be nothing wrong with the likes of:

.Values = Range(InstrumentName & "Close")

as long as that named range exists and IntrumentName contained a valid string.

(You have no valid SPCLose name in your attachement because it refers to a sheet SPData which I don't have.)

Can I have a broader view of what you're trying to do?

[Peter1961]

Dear p45cal,

It works, it works, thank you so much!

I red somewhere that by comparing different indexes, one can estimate the actual state of the market.
Let's say if 2-3-4 indexes start to decline or grow it is probable that the others will follow, so one can take positions eventually in the indexes in late.
It should also work for stocks also.

I wanted to see it on a chart.

I attach the modified file.

Thanks again for your precious help!

Best regards,

Peter

[Peter1961]

Dear Paul,

Thanks to you also for your help!

Peter