Iteration for sheet names

Hi all,

I hope someone can help me. I posted before but I guess the long message made it hard for people to help out.

Is there a way to refer to the Active sheet instead of its name : "Sheet1" in this code. This is because I am trying to iterate creating charts for all worksheet in the file.

I tried using ActiveSheet.Name, but it didnt work out

ActiveChart.SeriesCollection(1).XValues = "Sheet1!R2C19:r900C19"

I'm not really sure what you are doing here, but I don't think you can have an active sheet and an active chart. One of them is active, and the other isn't (by definition).

Why not try something like this (modify for your circumstances)

Sub AddCharts()
    Dim mySheet As WorkSheet
    For Each mySheet In Sheets
        mySheet.Select
        Charts.Add
        ActiveChart.ChartType = xlColumnClustered
        ActiveChart.SetSourceData Source:=mySheet.Range("B2:C4")
        ActiveChart.Location Where:=xlLocationAsObject, Name:=mySheet.Name
    Next mySheet
End Sub

Hi Mallycat,

Your way is great! It worked, and it's very simple too! The only thing: Is there a way where the source of the data could be taken from two column not adjacent to each other (i.e. not Column A and B), since when you specify range of data you can not specify except two columns adjacent. I tried setting the x-values but it's not working.

For example:
ActiveChart.SeriesCollection(1).XValues = mySheet.Range("A2:A4")

is not working.
Any help!

Thanks,

Hi everyone,

I solved my problem btw. Thanks very much Mollycat.

Just in case someone falls in the same situation, here is the code I am finally using:

Sub AddChart()
Dim mySheet As Worksheet
For Each mySheet In ActiveWorkbook.Worksheets
mySheet.Select
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=mySheet.Range("A2")
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Steering Angle"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Distance"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Steering Angle"
End With
ActiveChart.Legend.Select
Selection.Delete
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).XValues = mySheet.Range("s2:s91")
ActiveChart.SeriesCollection(1).Values = mySheet.Range("j2:j91")


ActiveChart.Location Where:=xlLocationAsObject, Name:=mySheet.Name
Next mySheet

End Sub