VBA to Rename sheets based on cell value in the sheet

ddander54

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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,647
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,799
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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
78
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,799
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,840
Messages
5,766,729
Members
425,375
Latest member
Bradleyckx

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