CODEWITHSUNDEEP
excelvbaworksheetexclude-constraint
Kenna
Kenna

Reputation: 1

Exclude one sheet from being looped through by VBA Macro code

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

Upvotes: 0

Views: 865

Answers (1)

Alexey
Alexey

Reputation: 416

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

Upvotes: 1

Related Questions