Type Mismatch in For Each Next Loop of Group Containing Different Types VBA Excel

Question

I am writing a macro in Excel to extract particular information found on various worksheets in different workbooks. The only issue is that the worksheets are not always named exactly consistently. Their name's do, however, contain the common string of "ExpandedEngReport". To solve this I am attempting to search for the worksheet containing this string using a FOR loop with the Like command to compare the sheet names to the partial string of "ExpandedEngReport".

This snippet of code returns a type mismatch error. I think this might be due to the various types found in the ActiveWorkbook.Sheets group (see attached picture 1) in which the For loop is searching.

' Find and Select ExpandedEngReport Worksheet
Dim ws As Worksheet
Dim flg As Boolean
For Each ws In ActiveWorkbook.Sheets
    If ws.Name Like "*ExpandedEngReport*" Then
        ws.Select Not flg
        flg = True
        GoTo CONTINUE
    End If
Next ws

The label CONTINUE leads to the set of operations I would like to perform on this sheet once found and selected.

The ActiveWorkbook.Sheets Group contains many different types of objects. Could this be linked to the issue? Is there any way arround this?

Answer 1

you have dimmed Dim ws As Worksheet and using it as a loop variable through Sheets collection, which can contain both Chart or Worksheet objects

so the fix is

Dim ws As Worksheet
...
For Each ws In ActiveWorkbook.Worksheets