Running Sub on only selected sheets

[Monaco]

Now I have good code that searches through each sheet in the workbook and runs a routine, but I have added some sheets where this sub is not necessary to have run there. How can I specify the specific sheets I want this to run in? (They are named sheets like "EG-P-GIJ - S").

Sub CheckBalances()
Dim ws As Worksheet
Dim lngRow As Long
Dim lngCount As Long
Dim FormulaRange As Range
Dim NotSentMsg As String
Dim MyMsg As String
Dim SentMsg As String
Dim MyLimit As Double
Dim Sheet As String
Dim current As Variant

For Each current In Worksheets
MsgBox current.Name
If current.Range("C80").Value <> 0 Then
current.Activate
Mail_small_Text_Outlook
MsgBox current.Name
End If
Next
End Sub

 

[mumps]

You can set up an array of the sheets that you want to work with like this:

Dim ws As Worksheet
For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4"))
    MsgBox ws.Name
    If ws.Range("C80").Value <> 0 Then
        ws.Activate
        Mail_small_Text_Outlook
        MsgBox ws.Name
    End If
Next ws

Just insert your sheet names.

 

[JLGWhiz]

There are several approaches to do what you want. You can use a list for a lot of sheets you want to search and refer to the cells one at a time in a loop to do the the search. Or, if you only have two or three additional sheets which you do not want to search you can exclude them in the code like:

Dim sh As Worksheet
For Each sh In ThisWorkbook.Sheets
 If sh.Name <> "Sheet1" And sh.Name <> "Sheet2" And sh.Name <> "Sheet3" Then
  'Do something
 End If
Next

 

[Monaco]

I have that in there now and have removed "For Each current In Worksheets"

When it runs it stops at MsgBox current.Name and says "Object Required"

Do you know the cause of that?

 

[JLGWhiz]

I have that in there now and have removed "For Each current In Worksheets"

When it runs it stops at MsgBox current.Name and says "Object Required"

Do you know the cause of that?

Please post the code that throws the error.