Is it possible to create a custom worksheet collection in VBA. That is I
normally loop thru worksheets as follows:
For Each wrksht In ActiveWorkbook
Next
But what if I want to loop thru certain sheets which I will define by items
in listboxes chosen by the user. I know I can create a collection and add
items to it but I am not sure how access that collection using the For Loop
above.
Does this mean I have to build a class module to create a custom worksheet
class?
Any ideas?
Thanks
EM
Hello Excel Monkey,
You don't need to create a Class Module for this. In this example, all
the code is in a Standard VBA Module. To make the collection available
throughout the project, the collection object variable is declared as
public. The LoadMyWorksheets macro puts the sheets you want into your
collection. You can then reference them using this format ...
MyWorksheets("sheet name")
This example loads 2 worksheets each from different workbooks.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Module Code
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Public MyWorksheets As Object
Sub LoadMyWorksheets()
Set MyWorksheets = New Collection
With MyWorksheets
.Add ThisWorkbook.Worksheets("Sheet1"), "Sheet1"
.Add Workbooks("Code For Lookup.xls").Worksheets("Sheet4"),
"Sheet4"
End With
End Sub
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Example
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sub ReadData()
X = MyWorksheets("Sheet4").Range("A1").Value
End Sub
