Exclude specific sheets from loop code

[Javi]

Hi All,

I'm looking for some assistance with modifying this code.

I would like to exclude a few sheets from the loop. Specifically the sheets code "Sheet1" "Sheet7" and "Sheet8" this is the code identifier not sheet name.

Any help or direction would be appreciated thanks.

Sub tabname()
Dim ws As Worksheet
For Each ws In Worksheets
    On Error Resume Next
    If Len(ws.Range("B17")) > 0 Then
        ws.Name = ws.Range("B17").Value
    End If
    On Error GoTo 0
    If ws.Name <> ws.Range("B17").Value Then
        MsgBox ws.Name & " Was Not renamed, the suggested name was invalid"
    End If
Next
End Sub

 

[Tom.Jones]

Hi,

try this:

Sub tabname()
Dim ws As Worksheet
For Each ws In Worksheets
    On Error Resume Next
    If ws.Name <> "Sheet3" and ws.Name <> "Sheet5" then
    If Len(ws.Range("B17")) > 0 Then
        ws.Name = ws.Range("B17").Value
    End If
    End If
    On Error GoTo 0
    If ws.Name <> ws.Range("B17").Value Then
        MsgBox ws.Name & " Was Not renamed, the suggested name was invalid"
    End If
Next
End Sub

 

[Peter_SSs]

.. exclude a few sheets from the loop. Specifically the sheets code "Sheet1" "Sheet7" and "Sheet8" this is the code identifier not sheet name.

See if this is it.

Sub tabname()
  Dim ws As Worksheet

  For Each ws In Worksheets
    Select Case ws.CodeName
      Case "Sheet1", "Sheet7", "Sheet8"
      Case Else
        On Error Resume Next
        ws.Name = ws.Range("B17").Value
        On Error GoTo 0
        If ws.Name <> ws.Range("B17").Value Then MsgBox ws.CodeName & " (" & ws.Name & ") Was Not renamed, the suggested name (" & ws.Range("B17").Value & ") was invalid"
    End Select
  Next ws
End Sub

Note that as well as addressing the exclusion of certain sheets, I have also removed the test ..

If Len(ws.Range("B17")) > 0 Then

.. since you cannot rename a sheet to a zero length string anyway.