loop through sheets

[hilss]

I know this question must've been asked before, but I can't find the answer anywhere...

In any event, I have 50 sheets. I have given them names in excel different than "Sheet1" "Sheet2" through "Sheet50"... they are called "alpha", "beta", "gamma" etc...

under the VB editor, under VBA project, under "Microsoft Excel Objects", I have the following:

Sheet01 (alpha)
Sheet02 (beta)
Sheet03 (gamma)
etc..

Note that I've renamed the Sheet as sheet01 to allow for the objects to be ordered...

to loop, I do the following:

I want to set the cell A1 to 1 in sheets 15 through 25 only:

For i = 15 to 25

sheets(i).Range("A1") = 1

Next

but in the event that a user takes the sheet "alpha" (which is sheet(1)), and puts in between sheets 15 and 25, the code will no longer work.

I might've confused you by all the above, so if you need clarification, please let me know.

I can sum this up by the following request:

I would like a loop that goes through a number of sheets without being impacted by the arrangement of the sheets in excel.

Thanks,
hilss

 

[VoG]

Try like this (small number of sheets in example):

Sub a()
Dim sh As Worksheet
For Each sh In Sheets(Array("Alpha", "Beta", "Gamma"))
    sh.Range("A1").Value = 1
Next sh
End Sub

 

[hilss]

Many Many thanks...

 

[hilss]

a follow-up question if I may... could we make it a bit more robust? So in the event that someone changes the sheet from "Alpha" to "Omega", the code above won't function correctly.

Thanks VoG II,
hilss

 

[VoG]

Something like this perhaps:

Sub a()
Dim sh As Worksheet
For Each sh In Sheets(Array("Alpha", "Beta", "Gamma"))
    If WorksheetExists(sh.Name) Then
        sh.Range("A1").Value = 1
    Else
        MsgBox "Error: " & sh.Name & " does not exist!"
    End If
Next sh
End Sub


Function WorksheetExists(WSName As String) As Boolean
    On Error Resume Next
    WorksheetExists = Worksheets(WSName).Name = WSName
    On Error GoTo 0
End Function