Sheet(1) in formula?

[Ago]

How can i make this code work?
I need to link the values to Sheet(1). The file is a CSV-file and because of this i know sheet1 has the same name as the filename.


Sub Macro1()
wkbName = Application.Workbooks(1).Name ' 14-17-09.CSV
shtName = Left(wkbName, InStr(wkbName, ".") - 1) '14-17-09
LastRow = 1281

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""velH"""
ActiveChart.SeriesCollection(1).Values = "=" & shtName & "!$G$3:$G$" & LastRow
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=""velD"""
ActiveChart.SeriesCollection(2).Values = "=" & shtName & "!$I$3:$I$" & LastRow
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = "=""hMSL"""
ActiveChart.SeriesCollection(3).Values = "=" & shtName & "!$D$3:$D$" & LastRow
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(3).AxisGroup = 2

end sub


I have also tried these



ActiveChart.SeriesCollection(1).Values = "=shtName!$G$3:$G$" & LastRow
ActiveChart.SeriesCollection(1).Values = "=Sheet(1)!$G$3:$G$" & LastRow
ActiveChart.SeriesCollection(1).Values = "=" & Sheet(1) & "!$G$3:$G$" & LastRow
ActiveChart.SeriesCollection(1).Values = "=WorkSheets(1)!$G$3:$G$" & LastRow


And so on. What am i missing here

[mbarron]

try
ActiveChart.SeriesCollection(1).Values = "=" & Sheet(1).Name & "!$G$3:$G$" & LastRow

[Ago]

It stops and marks Sheet, and i get a error message.

Compile error:
Sub or function not defined

[Ago]

Might aswell change my username to moron!

Found the problem
This is what i have been trying
ActiveChart.SeriesCollection(1).Values = "=" & shtName & "!$G$3:$G$" & LastRow
and this is the working code
ActiveChart.SeriesCollection(1).Values = "='" & shtName & "'!$G$3:$G$" & LastRow
Notice that i forgot the ' sign.
:banghead:

[Bob Phillips]

Might aswell change my username to moron!

Found the problem
This is what i have been trying
ActiveChart.SeriesCollection(1).Values = "=" & shtName & "!$G$3:$G$" & LastRow
and this is the working code
ActiveChart.SeriesCollection(1).Values = "='" & shtName & "'!$G$3:$G$" & LastRow
Notice that i forgot the ' sign.
:banghead:

I don't think so. We all make mistakes, being able to see the wood for the trees, that is not moronic.