For each syntax using a worksheet array variable
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
"Ralph Heidecke" wrote in message
...
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.
|