My "If Not" Statement is Not Accomplishing My Desired Result

[Ark68]

I have this code:

Sub staffintegrity()
'Stop
    Dim rngpda As Range
    Dim rwpdaed As Long 'last row of pda range
    Dim bunm As String
    
'hide unstaffed crews
    For Each ws9 In wb_data.Worksheets
        Application.DisplayAlerts = False
        Debug.Print ws9.Name
        bunm = ws9.Name
        If ws9.Name Like "Sheet*" Then
            ws9.Delete
            Debug.Print bunm & " deleted."
            'Exit For
        ElseIf ws9.Name = "Services" Then
            ws9.Delete
            Debug.Print bunm & " deleted."
            'Exit For
        ElseIf ws9.Tab.Color = vbRed Then
            ws9.Visible = xlhiddensheet
            Debug.Print bunm & " hidden."
            'Exit For
        End If
        Application.DisplayAlerts = True
    Next ws9

    cntsh = 0 'count of visible sheets
    For Each ws9 In wb_data.Worksheets
        If ws9.Visible = xlSheetVisible Then
            If Not ws9.Name Like "EV*" Then
                cntsh = cntsh + 1
                With ws9
                    . . . <code> . . .
                End With
            End If
            ttsnr = ttsnr + tsnr
            MsgBox ccnt & " cells analysed in worksheet: " & ws9.Name & Chr(13) & tsnr & " invalid names were replaced.", vbInformation, "INTEGRITY: OK    Worksheet: " & ws9.Name
            tccnt = tccnt + ccnt
        End If
    Next ws9
    MsgBox tccnt & " cells analysed in " & cntsh & " worksheets." & Chr(13) & ttsnr & " invalid names were replaced.", vbInformation, "INTEGRITY: OK    Worbkbook: " & wb_data.Name
End Sub

In an effort to exclude worksheets named "EVE1", "EVE", EVL", I added the if/endif in blue. But this doesn't appear to be working as those sheets are still being included.

 

[Alex Blakenburg]

Is there any chance your sheet names actually start with "Ev" not "EV" ?
"Like" is case sensitive.

If that is the case try:

If Not UCase(ws9.Name) Like "EV*" Then

 

[Ark68]

Not really as it doesn't appear to be the workbook with the relevant sheets in it...

Sorry Rory, at the time, providing that workbook would have been providing data.
Anyway, here is that file you need stripped of any data.

Link to Worksheet

Is there any chance your sheet names actually start with "Ev" not "EV" ?
"Like" is case sensitive.

No. I don't think so.

 

[RoryA]

It's not processing sheets whose names match that criterion. However, your Msgbox statement is outside the End If statement, so it probably looks like it is.

 

[Ark68]

Oh, I see I think. But the message box does include the number of cells checked, which I believe matches the actual cell count of the sheets it's supposed to get. Maybe. Perhaps that value (ccnt) remains from the previously processed sheet.

I'll start by moving my Msgbox to a hopefully more appropriate spot.

Thanks again Rory!

 

[RoryA]

Perhaps that value (ccnt) remains from the previously processed sheet

Yep.

 

[Ark68]

Hi Rory, felt I should say that your suggestion was the solution.
Thank you!