2 Conditions to Exclude Sheets in VBA

[pbt]

Snippet of what I have:

 With Range("A5")
    For Each sht In ActiveWorkbook.Worksheets
        If Not (sht.Name = "INFO" Or sht.Name = "FAX" Or sht.Name = "SUMMARY" _
        Or sht.Name = "PAG" Or sht.Name = "BUDGET" Then
            If Worksheet.Range("M211") = True Then
            ActiveCell.Value = sht.Name
            ActiveCell.Offset(1, 0).Select
        End If
        End If
    Next sht

I am making a list of certain sheets starting in A5. The If Not part worked fine until I put in the line refering to Range("M211")

"M211" changes from True to False depending on if a checkbox is checked or not.

If it is True I want the sheet name in my list. If False not in the list. Error message says Object required. I can't figure it out

Thanks
Harry

 

[Smitty]

Try:

If sht.Range("M211") = True Then

Note "sht", not "Worksheet".

HTH,

Smitty

 

[pbt]

Thanks Smitty,

That did the trick
Oh how I feel so dumb at times

Harry

 

[NateO]

Hi Harry,

You were missing a closing Parenthesis, so I assume that's aircode? And, are you sure you want 'Worksheet' as a Worksheet Object qualifier?

How about something along the following lines:

For Each sht In ThisWorkbook.Worksheets
    Select Case sht.Name
        Case "INFO", "FAX", "SUMMARY", "PAG", "BUDGET"
        Case Else
            If sht.Range("M211") = True Then
                Cells(i + 5, 1).Value = sht.Name
                i = i + 1
            End If
    End Select
Next sht

*Untested*

 

[pbt]

Thanks Nate

Yea, that missing Parenthesis was because I was shortening my post and forgot to close parenthesis.

Tried you code and it works just fine and a little quicker too.

Harry