VB to rename tabs based on cell information in first tab

[Kiwigirl66]

I have a multi tab spreadsheet which I would like to be able to automatically update the names on tabs 2 through to 13 but leaving the last few tabs names as they are.
I have called the first tab Date_Year with Col A being the year and Col B being the month
A2 = 2024 B2 = October
A3 = 2024 B3 = November
A4 = 2024 B4 = December
A5 = 2025 B5 = January
and so on up to 2025-Sept.
Based on the above, Tab 2 would be called Oct 2024, Tab 3 would be called Nov 2024 etc
With each year, I would change Cells A2-4 to the next year ie 2025 and Cells A5 onwards to 2026
I have seen a previous VB that works similiar but not quite doing what I need.
Could anyone please provide assistance?

 

[DanteAmor]

Try this:

Sub RenameSheets()
  Dim sName As String
  Dim i As Long
  For i = 2 To 13
    sName = Left(Range("B" & i).Value, 3) & " " & Range("A" & i).Value
    Sheets(i).Name = sName
  Next
End Sub

 

[Kiwigirl66]

Thanks DaniteAmor, it would not work for me, must have done something wrong my end.
The below works if I have the information on the same tab that I want renamed.
How do I modify to get the information from the first tab called Date
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C2")) Is Nothing Then

If Range("C2") = Empty Then
ActiveSheet.Name = "Client Unspecified-" & ActiveSheet.Index
Else
ActiveSheet.Name = Range("C2")
End If

End If
End Sub

 

[Trebor76]

Hi Kiwigirl66,

Welcome to MrExcel!!

I think we just need to add which sheet has the dates to Dante's code:

Option Explicit
Sub RenameSheets()
    
    Dim wsSrc As Worksheet
    Dim sName As String
    Dim i As Long
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    Set wsSrc = ThisWorkbook.Sheets("Date")
    
    For i = 2 To 13
        sName = Left(wsSrc.Range("B" & i).Value, 3) & " " & wsSrc.Range("A" & i).Value
        Sheets(i).Name = sName
    Next i
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
End Sub

Regards,

Robert

 

[DanteAmor]

it would not work for me

What doesn't work for you?
It sends you an error, what does the error say?

Based on the above, Tab 2 would be called Oct 2024, Tab 3 would be called Nov 2024 etc

The macro does what you asked for. Rename the sheets from the second sheet up to the thirteenth sheet.

The below works if I have the information on the same tab that I want renamed.

I don't know what that code has to do with your OP.


---------------

How do I modify to get the information from the first tab called Date

Simply refer to the sheet:

Sub RenameSheets()
  Dim sName As String
  Dim i As Long
  For i = 2 To 13
    sName = Left(Sheets("Date").Range("B" & i).Value, 3) & " " & Sheets("Date").Range("A" & i).Value
    Sheets(i).Name = sName
  Next
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --