Ago
10-19-2010, 09:46 AM
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
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