Manage groups of WS

[Guest]

Life used to be so simple. Not so now.

I used to have a lovely list of 31 WS and have my code cycle through each
one to find some information. Now I find that these 31 WS are actually three
Groups of 8 WS + one of 7 WS. What I used to do on all 31 WS now has to be
done on each Group.

I've had no problem initializing the four groups Dimmed as Sheets, and named
shtList2000, shtList2001, etc. However, I am having the very dickens of a
time trying to build a For Each..Next Loop that will work with the groups.

All contributions gratefully accepted!

 

[JE McGimpsey]

One way:

Dim vSheets As Variant
Dim i As Long
Dim j As Long
vSheets = Array(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", _
"Sheet5", "Sheet6", "Sheet7", "Sheet8"), _
Array("Sheet9", "Sheet10", "Sheet11", "Sheet12", _
"Sheet13", "Sheet14", "Sheet15", "Sheet16"), _
Array("Sheet17", "Sheet18", "Sheet19", "Sheet20", _
"Sheet21", "Sheet22", "Sheet23", "Sheet24"), _
Array("Sheet25", "Sheet26", "Sheet27", "Sheet28", _
"Sheet29", "Sheet30", "Sheet31"))
For i = LBound(vSheets) To UBound(vSheets)
For j = LBound(vSheets(i)) To UBound(vSheets(i))
Worksheets(vSheets(i)(j)).Range("A1").Value = _
"Group " & i & " Sheet " & j
Next j
Next i

 

[Guest]

Thanks -- even as you were posting that, I came up with a different one (BTW
the inner loop "J" is handled in a sub-process), but I shall hang on to yours
because I haven't been using LBound and UBound, and need to learn more about
them.

My solution was to do..

For intSheetsIndex = 1 to 4
Select Case intSheetsIndex
Case 1
Set shtCurSheet = shtList2003
Case 2
<etc.>
End Select
Next intSheetsIndex