Skipping over certain sheets in a For Each loop

**NicholasL** · 07-12-2013, 03:05 PM

Previously I had been shown a trick to delete all unprotected cells in my workbook.
The problem is, I want to to skip over a certain sheet, can I skip over a certain sheet with For Each loops?


Sub emptyUnlocked()
    
     Dim ws As Worksheet, bVBProtected As Boolean
     
     For Each ws In Worksheets
     
        If Not ws.ProtectContents Then
            ws.Protect
            bVBProtected = True
        End If
        
        'mikerickson's trick
        On Error Resume Next
        ws.UsedRange = ""
        On Error GoTo 0
        
        If bVBProtected Then
            ws.Unprotect
            bVBProtected = False
        End If
        
    Next ws
    
End Sub

**JOHN H. DAVIS** · 07-12-2013, 03:09 PM

Maybe:

Sub Nicholast()
    
     Dim ws As Worksheet, bVBProtected As Boolean
     
     For Each ws In Worksheets
        If ws.Name <> "THE NAME OF THE SHEET YOU WANT TO SKIP" Then
     
        If Not ws.ProtectContents Then
            ws.Protect
            bVBProtected = True
        End If
        
        'mikerickson's trick
        On Error Resume Next
        ws.UsedRange = ""
        On Error GoTo 0
        
        If bVBProtected Then
            ws.Unprotect
            bVBProtected = False
        End If
        End If
    Next ws
    
End Sub

**NicholasL** · 07-12-2013, 03:15 PM

Oh, good call!
Thanks, worked great!

**Leith Ross** · 07-12-2013, 03:10 PM

Hello NicholasL,

Sub emptyUnlocked()
    
     Dim ws As Worksheet, bVBProtected As Boolean
     
     For Each ws In Worksheets
     
        Select Case ws
          ' Names of worksheets to skip
            Case Is = "Sheet1", "Sheet4"
                ' Do nothing.
            Case Else
                If Not ws.ProtectContents Then
                    ws.Protect
                    bVBProtected = True
                End If
        
                'mikerickson's trick
                On Error Resume Next
                    ws.UsedRange = ""
                On Error GoTo 0
        
                If bVBProtected Then
                    ws.Unprotect
                    bVBProtected = False
                End If
        End Select
        
    Next ws
    
End Sub

**Richard Buttrey** · 07-12-2013, 03:12 PM

Hi,

Yes, just add an additional IF test

Sub emptyUnlocked()
    
     Dim ws As Worksheet, bVBProtected As Boolean
     
     For Each ws In Worksheets
        If ws.CodeName <> "code_name_of_sheet_to_skip"     
            If Not ws.ProtectContents Then

            'rest of code
            End If            
        End If


    Next ws
    
End Sub

Note. Use the VBA sheet code name rather than the sheet tab name in case someone should change the tab name.