VB to rename tabs based on cell information in first tab

Kiwigirl66

New Member
Joined
Oct 13, 2024
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Try this:

VBA Code:
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
 
Upvote 0
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
 
Upvote 0
Hi Kiwigirl66,

Welcome to MrExcel!!

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

VBA 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
 
Upvote 0
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:
Rich (BB code):
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
----- --
 
Upvote 0

Forum statistics

Threads
1,224,596
Messages
6,179,807
Members
452,943
Latest member
Newbie4296

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