loop through sheets

hilss

Active Member
Joined
May 22, 2007
Messages
379
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try like this (small number of sheets in example):

Code:
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

Active Member
Joined
May 22, 2007
Messages
379
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Something like this perhaps:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,921
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top