ExcelBanter - View Single Post - Looping Through Worksheets Given in a List?

Marc T

thanks for the help!

"Mike H" wrote:

Marc,

You could do it like this

Sub Macro3()
For Each myCell In Sheets("Sheet1").Range("E3:H6")
If myCell.Value < "" Then
On Error Resume Next
Set mysheet = Sheets(CStr(myCell))
If mysheet Is Nothing Then
MsgBox CStr(myCell) & " doesn't exist"
End If
Sheets(CStr(myCell)).Select
' .....rest of working code goes here.....
End If
Next myCell
End Sub

Mike

"Marc T" wrote:

Thanks a lot Mike, that works great!

One last thing that's puzzling me. Is there any way to alter the If
statement to also check if 'myCell.Value' does not exist as a worksheet?

Marc

"Mike H" wrote:

Marc,

Because you are selecting sheets then this must go in a general module.
Alt+F11 topn vb editor. Right click 'ThisWorkbook' and insert module and
paste the code in there. note that I've also qualified the range for E3 - H6
being in sheet1 so change to suit but you must qualify it.

Note it is unlikely you will actually need to select the sheet to do what
you want

Sub Macro3()
For Each myCell In Sheets("Sheet1").Range("E3:H6")
If myCell.Value < "" Then
Sheets(CStr(myCell)).Select
' .....rest of working code goes here.....
End If
Next myCell
End Sub

Mike

"Marc T" wrote:

Hi All,

I have the following code which is not currently working, I was wondering if
someone could spot the (probably obvious) error?

What I have is a list of worksheet names in cells range E3:H6. I want the
macro to step through these and perform actions in each worksheet. I also
want it to check if the worksheet actually exists before performing any
actions.

At the moment the macro halts at the Sheets(myCell).Select part where I'm
trying to refer to the worksheet name.

Any help would be much appreciated!
Marc

Sub Macro3()

For Each myCell In Range("E3:H6")

If myCell.Value < "" Then

Sheets(myCell).Select

.....rest of working code goes here.....

End If

Next myCell

End Sub