For each syntax using a worksheet array variable

[Ralph Heidecke]

I have created abn array variable with type Worksheet to execute the same
code on a set of worksheet. I would like to use the the For Each loop to
execute the code but I'm unsure of the syntax.

the variable declaration:

Dim wSheet(7) As Worksheet

Set wSheet(1) = ActiveWorkbook.Sheets("PROG")
Set wSheet(2) = ActiveWorkbook.Sheets("AUX")
Set wSheet(3) = ActiveWorkbook.Sheets("SMU")
Set wSheet(4) = ActiveWorkbook.Sheets("OPS")
Set wSheet(5) = ActiveWorkbook.Sheets("STAFF")
Set wSheet(6) = ActiveWorkbook.Sheets("BUS")
Set wSheet(7) = ActiveWorkbook.Sheets("ADMIN")

.....

so at this point I'm not sure how to write the loop

I was thinking something like:


For Each wSheet in wSheet
' a bunch of code to do the same thing with each sheet
Next wSheet

.... but intuituvely it doesn't seem right.

For info - there are 9 sheets in the work book; the 7 above have the same
structure the other 2 do not.

TIA.

 

[Doug Glancy]

Ralph,

Try somthing like this:

Sub test()

Dim ws_array
Dim ws As Worksheet
Dim i As Long

ws_array = Array(Worksheets("Sheet1"), Worksheets("Sheet2"))

For i = LBound(ws_array) To UBound(ws_array)
ws_array(i).Range("A1") = "wooza"
Next i

End Sub

hth,

Doug