Looping Through Names
 

Dim varr as Variant
varr = Array("SheetA","Houses","Dogs")
for i = lbound(varr) to ubound(varr)
set sh = Worksheets(varr(i))
msgbox sh.Range("A1").Address(external:=true)
Next

--
Regards,
Tom Ogilvy

"The Wonder Thing" wrote in
message ...
Hello All.

Just wondering if there's a simpler way to do loop through a bunch of
names. Say the names of various worksheets. Right now I use:

Do While mySheetIndex <= 5
mySheetIndex = mySheetIndex +1
If mySheetIndex = 1 Then mySheet="Worksheet Name 1"
If mySheetIndex = 2 Then mySheet="Worksheet Name 2"
If mySheetIndex = 3 Then mySheet="Worksheet Name 3"
If mySheetIndex = 4 Then mySheet="Worksheet Name 4"
If mySheetIndex = 5 Then mySheet="Worksheet Name 5"
Worksheets(mySheet).Range("A1").Value = "This is currently sheet #: " &
mySheetIndex
Loop

I have to use the worksheet names, because they do get moved around a bit.
And of course I generally have a lot more code I want to execute per sheet
than just that one line. I was thinking something along the lines of an
array that contained all the names of the sheets I wanted to target (because
there are some I don't want to), and then I could just define the array up
at the begginning and do something like:

For each sht in ActiveWorkbook.Worksheets
If sht.Name Like [One of the values in mySheetArray] Then
Worksheets(sht).Range("A1").Value = "This sheet.... etc."
End If
Next

But I can't get it to work.
Anyways, thanks in advance for your help!