Exclude one sheet from being looped through by VBA Macro code

Question

I want to exclude 1 worksheet from being looped in the code, how do I go about adding/doing that? I want to exclude my mailing list from being run through the code ("WB Mailling List"). I've tried a couple different suggestions that I found in different forums, but none really seemed to have work for me. Is there a simple and easy way to exclude one sheet from being looped through? I'm new to vba, so could really use the help! Thank you!

Option Explicit
Sub Main_AllWorksheets()
Dim sh As Worksheet, i As Long, shtsRotations As String
Dim shtsFunctions As String, shtsOK As String
Dim shtsManufacture As String


For Each sh In ActiveWorkbook.Worksheets

    If Application.CountIf(sh.Range("O3:O70"), "<1") > 0 Then
        shtsRotations = shtsRotations & vbLf & sh.Name
    Else
        shtsOK = shtsOK & vbLf & sh.Name & " (Rotations)"
    End If

    If Application.CountIf(sh.Range("P3:P70"), "<1") > 0 Then
        shtsFunctions = shtsFunctions & vbLf & sh.Name
    Else
        shtsOK = shtsOK & vbLf & sh.Name & " (Functions)"
    End If

    
     If Application.CountIf(sh.Range("Q3:Q70"), "<1") > 0 Then
        shtsManufacture = shtsManufacture & vbLf & sh.Name
    Else
        shtsOK = shtsOK & vbLf & sh.Name & " (Manufacturing Date)"
    End If

Next sh
 Dim myDataRng As Range


Set myDataRng = Worksheets("WB Mailing List").Range("A1:Z100" & Cells(Rows.Count, "S").End(xlUp).Row)

Dim cell As Range
Dim iCnt As Integer
Dim sMail_ids As String


For Each cell In myDataRng
    If Trim(sMail_ids) = "" Then
        sMail_ids = cell.Offset(1, 0).Value
    Else
        sMail_ids = sMail_ids & vbCrLf & ";" & cell.Offset(1, 0).Value
    End If
Next cell


Set myDataRng = Nothing         ' Clear the range.


If Len(shtsRotations) > 0 Then
    SendReminderMail sMail_ids, "Equipment rotations are due!", _
           "Hello Team, " & vbNewLine & vbNewLine & _
           "Check customer sheets: " & shtsRotations & vbLf & vbNewLine & _
           "In the attatched workbook, you can see what equipment needs to be rotated by the red dates, indicating their last rotation."

End If



If Len(shtsFunctions) > 0 Then
    SendReminderMail sMail_ids, "Equipment functions are due! ", _
           "Hello Team, " & vbNewLine & vbNewLine & _
           "Check customer sheets: " & shtsFunctions & vbLf & vbNewLine & _
           "In the attatched workbook, you can see what equipment needs to be functioned by the red dates, indicating their last function."
End If

If Len(shtsManufacture) > 0 Then
    SendReminderMail sMail_ids, "Manufacturing date has surpassed 3 years!", _
           "Hello Team, " & vbNewLine & vbNewLine & _
           "Check customer sheets: " & shtsRotations & vbLf & vbNewLine & _
           "In the attatched workbook, you can see what equipment has reached it's 3 years past manufacturing."
End If


If Len(shtsOK) > 0 Then
    MsgBox "These sheets are OK: " & vbLf & shtsOK, vbInformation
End If

 End Sub

`If sh.name <> "WB Mailling List" Then` as the first line inside the loop and another `End If` as the last line in the loop. — Scott Craner, Jul 22 '21 at 21:10

Alexey · Answer 1 · 2021-07-22T23:11:14.010

1

You should catch the sheet by name or id to skip it.

add this line after For ...

If Not sh.Name="WB Mailing List" Then ... End If

Please, change your For statement to this:

 For Each sh In ActiveWorkbook.Worksheets
    If Not sh.Name="WB Mailing List" Then
      If Application.CountIf(sh.Range("O3:O70"), "<1") > 0 Then
          shtsRotations = shtsRotations & vbLf & sh.Name
      Else
          shtsOK = shtsOK & vbLf & sh.Name & " (Rotations)"
      End If

      If Application.CountIf(sh.Range("P3:P70"), "<1") > 0 Then
          shtsFunctions = shtsFunctions & vbLf & sh.Name
      Else
          shtsOK = shtsOK & vbLf & sh.Name & " (Functions)"
      End If

    
      If Application.CountIf(sh.Range("Q3:Q70"), "<1") > 0 Then
          shtsManufacture = shtsManufacture & vbLf & sh.Name
      Else
          shtsOK = shtsOK & vbLf & sh.Name & " (Manufacturing Date)"
      End If
   End if
Next sh

edited Jul 22 '21 at 23:11

answered Jul 22 '21 at 21:22

Alexey

386
2
9

Don't know if that's why it was voted down and I'm not the man, but you really should avoid `Goto` when a simple `if...then` will do the job. – cybernetic.nomad Jul 22 '21 at 21:33
This is not a rule to avoid goto. This is my solution. – Alexey Jul 22 '21 at 21:34
And it is probably the opinion of the voter that goto is not the proper method to use here. A vote whether up or down is an expression of perceived quality. See: https://www.l3harrisgeospatial.com/Learn/Blogs/Blog-Details/ArtMID/10198/ArticleID/15289/Why-should-GOTO-be-avoided It is the major opinion of most coders that using goto where a simply If Then block can be used is a poor practice. – Scott Craner Jul 22 '21 at 21:41
it is even stated in Microsofts documentation that it should be avoided if possible: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/goto-statement – Scott Craner Jul 22 '21 at 21:42
Ok, there is no goto)) – Alexey Jul 22 '21 at 21:50
I don't think you should use `Not` when no objects are involved. I mean, you have a simple solution: `If sh.Name <> "WB Mailing List" Then`. Also, if you want to allow case-insensitivity (e.g. `A = a`) which is the 'natural behavior' when dealing with worksheet names, you could use [StrComp](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/strcomp-function) and do: `If StrComp(sh.Name, "WB Mailing List", vbTextCompare) = 0 Then`. – VBasic2008 Jul 22 '21 at 22:58
Thank you all very much! Problem solved!! :) – Kenna Jul 23 '21 at 13:06

Exclude one sheet from being looped through by VBA Macro code

1 Answers1