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.

 

[sparky2205]

Thanks for your solution JackDanIce.

I'm not sure what I was originally doing with the AND/OR Brackets scenario but it all works exactly as CJ said now. My Bad.

I converted this to run automatically when C4 was changed and placed the code in the Sheet Revised.

Private Sub worksheet_change(ByVal target As Range)

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

But, under these conditions, when I run my other macros on this sheet it just keeps running until I press ESC. Then the action does complete successfully.
If I place the tab rename code in ThisWorkbook and run it from a button on the screen the other macros run fine. I don't know why this is. Any ideas?

 

[JackDanIce]

Try:

Private Sub worksheet_change(ByVal target As Range)


Dim yr As Integer, i As Integer
Application.EnableEvents = False
    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
Application.EnableEvents = True
End Sub

Also, if using Excel 2007 or later, the compiler auto converts integers to longs, so generally integers are used less in VBA than long data types.

 

[sparky2205]

This worked. I used the Application.EnableEvents in the code for my other macros rather than the rename tab code (above).
This has sped up the operation and it now completes without manual intervention.
The user now only has to populate the year in C4 rather than also have to run a macro.
Looks like we have a winner folks.

Thank you both for your help with this. That's the finishing touches to my little project.
Have an immensely good day.

Best Regards,
Sparky.

 

[JackDanIce]

Glad its resolved Sparky