avoiding hidden sheets with sheet naming

[egris52788]

How do I get this sheet tab naming macro to avoid hidden sheets. I want it to only name the unhidden sheets?


Sub NameSheetss()
Dim I As Long
Dim ws As Worksheet
I = 1
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Sheet Names" Then ws.Name = Sheets("Sheet Names").Cells(I, 1)
I = I + 1
Next ws
End Sub

 

[xld]

Sub NameSheetss()
Dim I As Long
Dim ws As Worksheet
    I = 1
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Visible = clseheetvisible Then
            If ws.Name <> "Sheet Names" Then
                ws.Name = Sheets("Sheet Names").Cells(I, 1)
            End If
        End If
        I = I + 1
    Next ws
End Sub

 

[Greg Truby]

Oopsies:

        If ws.Visible = clseheetvisible Then

        If ws.Visible = xlSheetVisible Then

:wink:

 

[xld]

Oopsies indeed!

 

[jim may]

Sub NotVisiblesheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Not ws.Visible = True Then
MsgBox "Your Sheet " & ws.Name & " is hidden"
End If
Next
End Sub

 

[VoG]

In case Sheet Names contains invalid sheet names (or has blanks in column A)

Sub NameSheets()
Dim I As Long, ws As Worksheet
I = 1
For Each ws In ActiveWorkbook.Worksheets
    If ws.Visible = xlSheetVisible And ws.Name <> "Sheet Names" Then
        On Error Resume Next
        ws.Name = Sheets("Sheet Names").Cells(I, 1)
        On Error GoTo 0
    End If
    I = I + 1
Next ws
End Sub

 

[Greg Truby]

In case Sheet Names contains invalid sheet names (or has blanks in column A)

Oh, but that would *never* happen. Right?!

 

[VoG]

Oh, but that would *never* happen. Right?!

Absolutely never - but I'm a 'cautious coder'

 

[Greg Truby]

Actually, if I had a ¡DOH! emoticon, I woulda used it. I myself have made that exact recommendation (adding the RESUME NEXT/GOTO 0 around a worksheet rename) on probably a dozen threads over the years.