Manage groups of WS
 

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!
--
Dave
Temping with Staffmark
in Rock Hill, SC

Manage groups of WS
 

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


In article ,
Dave Birley wrote:

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!

Manage groups of WS
 

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
--
Dave
Temping with Staffmark
in Rock Hill, SC


"JE McGimpsey" wrote:

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


In article ,
Dave Birley wrote:

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!