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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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
Back
Top