2 Conditions to Exclude Sheets in VBA

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
Snippet of what I have:
Code:
 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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Try:

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

Note "sht", not "Worksheet".

HTH,

Smitty
 

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
Thanks Smitty,

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

Harry
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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:

Code:
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

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,400
Messages
5,601,462
Members
414,451
Latest member
jrose7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top