Create subset collection of worksheets?

[gravanoc]

We have a defined collection in Workbooks, Worksheets, and a few others, but is it possible to make our own subset collection or alternate data structure that only holds a few sheets? My reason for this is that I have a workbook with a lot of sheets. In perspective, after a few trials it would take approximately 330 sheets for the For Each loop to take 1 second to run through, assuming that's all it does. I don't have 330 sheets, but there is a lot of branching logic going on, and I'm just trying to cut down on some of the operational time. Trying to use a collection or dictionary haven't worked for me. There are some sheets that will never meet the condition I'm checking for in my loop, so it would make sense to me to create a collection of sheets without them.

 

[Jaafar Tribak]

You can store the relevant worksheets somewhere like in a module level collection or array etc once ... Then refer to them later on throughout your code.

Something like this might work:

Declare a module level variable for storing the relevant sheets.

Public oSheets As Sheets

Store the relevant sheets by index or name.

Set oSheets = Sheets(Array(1, 2, 6, 15))

Then when iterating the relevant sheet in your code:

Dim oSh As Worksheet
For Each oSh In oSheets
    MsgBox oSh.Name
Next