Refer to current Sheet within a macro

[Aldo]

Hello,


I just recorded this macro:


ActiveSheet.Shapes.AddChart.Select

ActiveChart.ChartType = xlXYScatterSmoothNoMarkers

ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(1).XValues = "=RawData_1!$A$2:$E$1500"

ActiveChart.SeriesCollection(1).Values = "=RawData_1!$B$2:$F$1500"


How do I get "RawData_1" to be RawData_# and refer to the current sheet that I am in?


Once I know how to refer to a sheet's name in code...can I just use a for loop to iterate through the sheets and plot a series for each?


Thanks,


Aldo

 

[Luke M]

Hopefuly this gives you some ideas.

[pre]

Sub Examples()
Dim aSheet As String
Dim AnotherSheet As String

aSheet = ActiveSheet.Name
AnotherSheet = Worksheets("Sheet1").Name
ActiveChart.SeriesCollection(1).Values = "=" & aSheet & "!$B$2:$F$1500"

'loop example
For i = 1 To 10
ActiveChart.SeriesCollection(i).Values = "=RawData_" & i & "!$B$2:$F$1500"
Next
End Sub

[/pre]

 

[Aldo]

I've been Ninja'd again, I think I got it.


Thanks,


Aldo