Creating custom workbook object

[tiredofit]

My workbook contains a number of worksheets. They are classified as either fruits or vegetables, eg Apple, Orange, Cucumber, Lettuce, etc.

I want to return the values in the cell A1 from only the Fruit worksheets.

I can do the following:

Dim ws As Worksheet

For Each ws In Thisworkbook.Worksheets

    If ws.CodeName <> wksCucumber.CodeName And ws.CodeName <> wksLettuce.CodeName Then Debug.Print ws.Range("A1").Value

Next ws

However, I don't want to have to specify a condition every time. Instead, I wanted to create a single object and then use it, for example:

Public Function FruitSheets() As Collection

    Dim Coll As Collection
    Set Coll = New Collection
    
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
    
       If ws.CodeName <> wksCucumber.CodeName And ws.CodeName <> wksLettuce.CodeName Then

            Coll.Add Item:=ws.CodeName
            
        End If
    
    Next ws
    
    Set FruitSheets = Coll
    
End Function

and then use it as follows:

Public Sub ChooseFruitsOnly()

    Dim ws As Worksheet
    
    For Each ws In Module1.FruitSheets
    
        Debug.Print ws.Cells(1, 1).Value
    
    Next ws

End Sub

but I get an error message:

Run-time error '424'

Object required

Is my idea possible and if so, how could I amend my code to make it work?

Thanks

 

[6StringJazzer]

You are not adding worksheets to your collection, you are adding the codenames of worksheets.

Try this to add the worksheet to the collection in Function FruitSheets:

            Coll.Add Item:=ws

 

[tiredofit]

You are not adding worksheets to your collection, you are adding the codenames of worksheets.

Try this to add the worksheet to the collection in Function FruitSheets:

            Coll.Add Item:=ws

Thanks, just what I wanted.