Looping Through Worksheets Given in a List?

[Marc T]

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

 

[Mike H]

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]

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]

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]

thanks for the help!

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

 

[Dave Peterson]

Option Explicit
Sub Macro3()
Dim MyCell as Range
dim wks as worksheet

For Each myCell In activesheet.Range("E3:H6").cells
If myCell.Value <> "" Then

set wks = nothing
on error goto 0
set wks = worksheets(cstr(mycell.value))
on error goto 0

if wks is nothing then
'skip it
else
wks.select
'rest of code
end if

end if

Next myCell

End Sub

But you don't usually need to select a sheet to work with it.

instead of:
wks.select
range("a1").value = "hi"

You could use:
wks.range("a1").value = "hi"