Dynamically Rename Worksheet Tabs

[sparky2205]

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.

 

[MrIfOnly]

Hi Sparky:

Try this is a copy of your workbook:

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

 

[MrIfOnly]

A couple changes to catch an invalid year being entered in C4:

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

 

[sparky2205]

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:

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:

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?

 

[MrIfOnly]

Yes, the logical test should be "AND": if condition is not "Revised" AND is not "Comparison" AND...etc.

Regards,

CJ

 

[sparky2205]

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:

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.

 

[MrIfOnly]

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

 

[JackDanIce]

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:

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.

 

[MrIfOnly]

If InStr("revised|comparison|chartfriendly|chart|do_no_delete", lcase$(.Name)) = 0 Then 
                  .Name = Replace(.Name, Right$(.Name, 4), yr)
            End If

Neat!

 

[JackDanIce]

Thanks!