VBA to Rename sheets based on cell value in the sheet

[ddander54]

I'm trying to rename sheets in an active workbook based on the value in the sheet. The problem for me is that in Sheet1 the value is in cell B7, in the rest of the sheets (sheet2 thru sheet'x'), the value is in cell B6.
This code changes all the sheet name perfectly, except that Sheet1 has the value in B7, rather than B6 for the rest.
Here is the code I'm trying to modify. The Sheet1 part works fine, but then the Sheet2 to SheetX part comes along and changes Sheet1. How do I say Sheet2 to SheetX?

Option Explicit


Sub RenameSheet()
Dim ws As Worksheet
For Each ws In Worksheets
    On Error Resume Next
    'Just change sheet name for Sheet1
    If Sheets(1).name = "Sheet1" Then
        ws.name = ws.Range("B7").Value
    End If
    'From Sheet2 to SheetX.....
    If Len(ws.Range("B6")) > 0 Then
        ws.name = ws.Range("B6").Value
    End If
    On Error GoTo 0
    If ws.name <> ws.Range("B6").Value Then
        MsgBox ws.name & " Was Not renamed, the suggested name was invalid"
    End If
Next
End Sub

Thanks in advance,
Don

 

[steve the fish]

All sorts of approaches but here is one that you could use. There are simpler ways but this is a nice one to learn:

For Each sh In ThisWorkbook.Worksheets
    Select Case sh.Name
        Case "Sheet1"
            MsgBox sh.Name & " so do this"
        Case Else
            MsgBox sh.Name & " so do something else"
    End Select
Next

 

[mumps]

Try:

Sub RenameSheet()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name = "Sheet1" Then
            ws.Name = ws.Range("B7").Value
        esle
            ws.Name = ws.Range("B6").Value
        End If
    Next
    Application.ScreenUpdating = True
End Sub

 

[ddander54]

Steve the Fish, Mumps,

Thank you both for your solutions. I was able to make both work. This is probably an age old question, but is there an advantage of one over the other when using If>Else rather than Case>Case Else?
Sorry Mumps, I have to give Steve the Fish the 'Solution' because he was first, but I'll probably be using your solution because IF>Else is more familiar to me at this point in my limited knowledge.

Thanks,
Don

 

[mumps]

In your case I don't think there is much difference. Sometimes using the "Select case" approach can be more efficient and shorten the code.