Dynamically Rename Worksheet Tabs

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
474
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi folks,
I am trying to create a macro to enable me to rename the tabs in a worksheet based on a cell in a different worksheet in the same workbook. The macro button will be located on the same sheet as C4.
I have a tab called Revised which has a year in cell C4.
I have 12 sheets Jan2018, Feb2018 etc...
I want to be able to rename the tabs when the year changes in C4.
I've been playing around with some code but haven't managed to get past a line or two without experiencing a type mismatch error when trying to populate a range. So not worth posting.

Any ideas would be very welcome.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi Sparky:

Try this is a copy of your workbook:

Code:
Private Sub CommandButton1_Click()    
Dim yr As Integer, i As Integer
    
    yr = Sheets("Revised").Range("C4").Value
    
    For i = 1 To Worksheets.Count
        With Sheets(i)
            If Not .Name = "Revised" Then
                .Name = Left(.Name, Len(.Name) - 4) & yr
            End If
        End With
    Next i
End Sub

Regards,

CJ
 
Last edited:
Upvote 0
A couple changes to catch an invalid year being entered in C4:

Code:
Private Sub CommandButton1_Click()
    Dim yr As String, i As Integer
    
    yr = Sheets("Revised").Range("C4").Value
    
    If Not Len(yr) = 4 Or Not IsNumeric(yr) Then
        MsgBox ("Please enter a valid year in cell C4")
        Exit Sub
    End If
    
    For i = 1 To Worksheets.Count
        With Sheets(i)
            If Not .Name = "Revised" Then
                .Name = Left(.Name, Len(.Name) - 4) & yr
            End If
        End With
    Next i
End Sub

Regards,

CJ
 
Upvote 0
Hi MrIfOnly,
firstly thank you for the very speedy response. This code works perfectly for me.
I modified it slightly as I actually have more than 1 other worksheet. I have used the following:
Code:
Sub UpdateTabName()
Dim yr As Integer, i As Integer
    
    yr = Sheets("Revised").Range("C4").Value
    
    For i = 1 To Worksheets.Count
        With Sheets(i)
            If Not .Name = "Revised" Then
                If Not .Name = "Comparison" Then
                    If Not .Name = "ChartFriendly" Then
                        If Not .Name = "Chart" Then
                            If Not .Name = "DO_NOT_DELETE" Then
                                .Name = Left(.Name, Len(.Name) - 4) & yr
                            End If
                        End If
                    End If
                End If
            End If
        End With
    Next i
End Sub
It does run rather slowly. No biggie, it'll only be required once a year. But for my own knowledge I tried the following to see if it would run faster:
Code:
Sub UpdateTabName2()
Dim yr As Integer, i As Integer
    
    yr = Sheets("Revised").Range("C4").Value
    
    For i = 1 To Worksheets.Count
        With Sheets(i)
            If Not .Name = "Revised" Or Not .Name = "Comparison" Or Not .Name = "ChartFriendly" Or Not .Name = "Chart" Or Not .Name = "DO_NOT_DELETE" Then
                .Name = Left(.Name, Len(.Name) - 4) & yr
            End If
        End With
    Next i
End Sub
But this doesn't work. On the first pass it ignores the If Not and proceeds to change the name of sheet Revised to Rev2018. I thought I was telling it to ignore sheets named Revised OR Comparison OR ChartFriendy" etc. But apparently not. Can you see my error?
 
Upvote 0
Yes, the logical test should be "AND": if condition is not "Revised" AND is not "Comparison" AND...etc.

Regards,

CJ
 
Upvote 0
Hi CJ,
using AND instead of OR resulted in the Revised sheet being skipped (which I want) but the next sheet, Comparison, being renamed Compar2018 (which I don't want).
I did get it to work with OR by enclosing the full statement in brackets:
Code:
Sub UpdateTabName2()
Dim yr As Integer, i As Integer
    
    yr = Sheets("Revised").Range("C4").Value
    
    For i = 1 To Worksheets.Count
        With Sheets(i)
            If (Not .Name = "Revised" And Not .Name = "Comparison" And Not .Name = "ChartFriendly" And Not .Name = "Chart" And Not .Name = "DO_NOT_DELETE") Then
                .Name = Left(.Name, Len(.Name) - 4) & yr
            End If
        End With
    Next i
End Sub

Many thanks for your help. Have a good day.
 
Upvote 0
Glad you got it working. Just curious though: you said that you were able to get it to work with OR by enclosing the full statement in brackets but the code you posted shows the use of AND. In my testing with two ineligible sheets it worked fine with using AND without enclosing in parenthesis.

Also, you may want to consider my code from post #3 with the safeguards in it. Otherwise, if a 3- or 5- digit number is entered for the year you may see some unwanted results.

Regards,

CJ
 
Upvote 0
Sparky, you may find it easier to test the sheet name using this approach, create a long string of names and test if the name is found within. If not, then rename, try:
Code:
Sub UpdateTabName()

    Dim yr   As Long
    Dim x    As Long
    
    yr = Sheets("Revised").Cells(4, 3).Value
    
    Application.ScreenUpdating = False

    For x = 1 To Worksheets.Count
        With Sheets(x)
            If InStr("revised|comparison|chartfriendly|chart|do_no_delete", lcase$(.Name)) = 0 Then 
                  .Name = Replace(.Name, Right$(.Name, 4), yr)
            End If
        End With
    Next x
    
    Application.ScreenUpdating = True
    
End Sub
Here the test is "does the sheet name exist in the string to search"? If it does, INSTR returns a positive integer (indicating position in string match is found), otherwise 0 for no match.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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
Back
Top