For Each Loop in an Array of Sheets

[Begbie]

Hi

I have written a bunch of code with the intention of throwing it into a For Each loop to apply the code to several different worksheets.
The problem I have is that I can't remember how to do it!

I have:

Dim Work_Sheet As Worksheet
For Each Work_Sheet In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))

CODE HERE

Next Work_Sheet


I can almost guarantee it is something simple that is wrong, someone help me please!

 

[TheNoocH]

this seems to work for me...

Sub WorksheetLoopArray()
Dim ws As Worksheet
     For Each ws In Worksheets(Array("Sheet1", "Sheet2"))
          MsgBox "Sheet Name - " & ws.Name & vbCr & "Worksheet Index # - " & ws.Index
      Next ws
End Sub

 

[mikerickson]

Array("Sheet1","Sheet2") contains strings, not worksheets. Perhaps something like this:

Dim Work_Sheet_Name as Variant
For Each Work_Sheet In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
   With Sheets(Work_Sheet_Name)
       CODE HERE
   End With
Next Work_Sheet

 

[Begbie]

Thanks! Knew it was something simple, I used "sheets" in stead of "worksheets"!!!

DOH!