xlLocationAsObject

[Peter H]

Hi, I have a question regarding adding a new chart. I'm
running a macro on one workbook that will create graphs in
another workbook. I'm trying to create a graph that will
display as part of the newest worksheet of the new
workbook. This macro will run every so often, inserting
new pages into the new workbook, which then would show
charts on that page.

My problem is with the "ActiveChart.Location
Where:=xlLocationAsObject, Name:=Worksheets(1)" line. Is
it only possible to use xlLocationAsObject with a sheet
name? I would much rather be able to use the index value
of the worksheets since, the names of the sheets will keep
incrementing as new pages are added.

I'll post the code below. Thanks in advance.




With Workbooks(Workbooks.Count).Worksheets(1)
Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = Worksheets
(1).Range("B2:B57")
ActiveChart.SeriesCollection(1).Values = Worksheets
(1).Range("A2:A57")
ActiveChart.Location Where:=xlLocationAsObject,
Name:=Worksheets(1)
ActiveChart.HasLegend = False
End With