WORKAROUND: How To: Creating Arrays of Excel Sheets ???

Esteemed Forum Participants and Lurkers:
===============================

EXCEL

I have an Excel Workbook with a bunch of sheets in 3 major categories. I can easily walk through the sheets and determine the category, but I can't seem to create Arrays of categorized sheets to work with.

Here is the template for what I want to do:

Code:

---

Dim asht As Worksheet
Dim i as Integer

'HOW TO DIM an Array of Sheets?
Dim ShtCat(3) as WorkSheets

For Each asht In ActiveWorkbook.Worksheets
    'Find the Category for each Sheet
    Select Case Left(asht.Name, 4)
        Case "Cat1"
            i = 1
        Case "Cat2"
            i = 2
        Case "Cat3"
            i = 3
    End Select
   
    'Add this sheet into Sheets Array ShtCat(i) ???

Next

---

Thank you for any and all comments, suggestions, and assistance.

I think you may be able to do it by declaring the array as Object or create a
collection of WorkSheet type.

Why not just use the Worksheets collection as is?

Thanks RobDog ...

Quote:

---

Originally Posted by RobDog888

Why not just use the Worksheets collection as is?

---

I don't use the Worksheets collection as is because I have to split it into 3 separate workbooks IN A SUBROUTINE. I have different sets of categories that get processed in the subroutine, so I have to do build these arrays of worksheets programatically.

... ...

The core of the question is "how to add another sheet to an Array of sheets?" Sort of like:

Code:

---

ShtArray(2) = ShtArray(2) + NewSht

---

ShtArray(2) would now consist of all previous sheets AND the New Sheet.

As a possible solution ... how can I EXTEND the set of selected sheets?

Code:

---

Sheets("Sheet1").Select
' Now, add another sheet (say, Sheet4) to the set of selected sheets!
?????????????????????????????

---

I could then work with the "Selection" set of sheets.

Rather than continuing to fight with arrays, I just did a sheet-by-sheet workaround ... This deals out sheets to 2 new files based on the contents of the Sheet Tab Name:

Code:

---

Option Explicit
Sub Deal_Out_Sheets()
'=============================================================================
'Create 2 new workbooks
Dim wbk_EAST As New Workbook
Dim wbk_WEST As New Workbook

'Create variables for referencing Worksheets
Dim abook As Workbook
Dim asheet As Worksheet

'Set a variable for the current workbook
Set abook = ActiveWorkbook

'Add 2 new workbooks for the 2 regions
'Create the workbooks with only 1 sheet, which is the MINIMUM
'  (zero sheets is not legal)
Application.SheetsInNewWorkbook = 1
Set wbk_EAST = Workbooks.Add
Set wbk_WEST = Workbooks.Add
'Rename the sheets from "Sheet1" to "NULL"
'This way if no other sheets get added to each Region workbook,
'  there will only be a "NULL" sheet in the book
wbk_EAST.Sheets(1).Name = "NULL"
wbk_WEST.Sheets(1).Name = "NULL"

'Scan the sheets in the original workbook and deal out the sheets
'For this test, I just used default sheets: Sheet1, Sheet2, etc.
For Each asheet In abook.Worksheets
  'In this case, look at the right-most character of the sheet name
  Select Case Asc(Right(asheet.Name, 1))
   
    'Sheets ending in 0, 2, 4, 6, 8
    Case 48, 50, 52, 54, 56 ' Even
      'Copy the sheet to end of the Workbook for this category
      asheet.Copy before:=wbk_EAST.Worksheets(wbk_EAST.Worksheets.Count)
     
    'Sheets ending in 1, 3, 5, 7, 9
    Case 49, 51, 53, 55, 57 ' Odd
      'Copy the sheet to end of the Workbook for this category
      asheet.Copy before:=wbk_WEST.Worksheets(wbk_WEST.Worksheets.Count)
   
  End Select
Next
'All of the sheets have been dealt out to the Region Files

'Disable all of the application warnings
Application.DisplayAlerts = False
'Force Delete of NULL sheet unless it is the only sheet in the book
If wbk_EAST.Sheets.Count > 1 Then wbk_EAST.Sheets("NULL").Delete
If wbk_WEST.Sheets.Count > 1 Then wbk_WEST.Sheets("NULL").Delete
'Force Save with Overwrite of the Region Files
'    >> IS THIS WISE???  <<
wbk_EAST.SaveAs Filename:="C:\Test\East.xls"
wbk_WEST.SaveAs Filename:="C:\Test\West.xls"
'Close the source book
abook.Close savechanges:=False
'  (We need to rename and/or move the original file)
'
'Turn Alert messages back on
Application.DisplayAlerts = True

'Close the new workbooks - changes were already saved in 'SaveAs'
wbk_EAST.Close savechanges:=False
wbk_WEST.Close savechanges:=False

End Sub

---

If anybody has any ideas or comments about extending arrays of sheets or extending selections of sheets, I would surely like to hear about them.