VBA to Rename sheets based on cell value in the sheet

ddander54

Board Regular
Joined
Oct 18, 2012
Messages
75
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?

VBA Code:
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,644
Office Version
  1. 365
Platform
  1. Windows
All sorts of approaches but here is one that you could use. There are simpler ways but this is a nice one to learn:

VBA Code:
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
 
Solution

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,648
Try:
VBA Code:
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

Board Regular
Joined
Oct 18, 2012
Messages
75
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,648
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.
 

Forum statistics

Threads
1,148,277
Messages
5,745,818
Members
423,980
Latest member
zimza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top