Rename worksheet tab based on cell value

I have a workbook that is a yearly time sheet. Every week is a separate tab. The tab names are the dates of every Friday of the year. In cell B2 in the first worksheet contains the date of the first Friday in January, 2014. That same cell on the next sheet is linked to the first cell +7. The third sheet is linked to the first tab +14, etc....

I set it up so that I only have to change the date of the first Friday in January and the rest of the sheets update their dates accordingly so in 2015 all the dates change in a flash. However, each worksheet's tab is also the date in B2, but not linked. What I want to do is also have the sheet tabs names changed based on the value of B2 on each sheet so I don't have to rename them manually.

Is there an easy way to do this with VBA?

Hi Tritekfire,

Try the below,

HTML Code: 

*Add reputation if my post helps and mark the thread as solved *

Which module to do place this? Do I copy it to each sheet?

Just a note, certain characters can't be used in sheet names. One of these characters is "/" which is common in date formats. If your date utilizes "/" you are going to need to modify the date (usually using "-" instead of "/")

Place it under module 1. You don't need to copy it to any sheet. It is an independent subroutine which will access each sheet accordingly. Please note my post above regarding characters.

I see what you mean about about the "/" character. The autodater works by increasing the date by 7 days on each sheet. Even though I formatted the cell to display "3-Jan", in the formula bar it displays 01/03/14. I don't have any other option like formatting the cell as text because on each sheet are the days of the week with the dates, and that feeds from that same cell B2. So I have to keep the date formatting. Does this mean I am hosed or is there another clever work around?

when I ran the vba I get a message that I cannot rename sheets to the same name. My very first sheet is a pivot table that combines all of the pivot tables on each sheet for each week to give a yearly summary. The name of this sheet is "Summary". When I run the macro it changes the name of the sheet to "01-00 1900". I assume it was trying to rename all of the sheets to this same thing, which means it's not working. Any ideas?

After re-reading through these posts, I can see that offered solution wouldn't work. However, I for one am confused by the vagueness of the situation. Perhaps you should list each of the current tab names and restate what they should be, apparently based on what is in cell B2.

A first guess is the sheet names will need to be tested first to see if they are in the format of a date. And apparently some sheet names will need to be the value of B2 +7, then +14, then + 21, etc?

Try this:

Sub Rename()
Dim ws As Worksheet, wsCheck As Worksheet
Dim strName As String

Application.ScreenUpdating = False

For Each ws In Worksheets
    If IsDate(ws.Range("B2").Value) Then
        strName = Format(ws.Range("B2").Value, "mm-dd-yyyy")
    Else
        strName = ws.Range("B2").Value
    End If
    
    Set wsCheck = Nothing
    On Error Resume Next
    Set wsCheck = Sheets(strName)
    If wsCheck Is Nothing Then
        ws.Name = strName
    Else
        MsgBox ("The sheet " & strName & " already exists.  Click OK to continue.")
    End If
    On Error GoTo 0
Next ws

Application.ScreenUpdating = True
    
End Sub

That worked like a charm stnkynts!!! I actually changed the date to just "mm-dd", and it looks just like I had it. All I have to do is change the date of B2 on the first sheet, and it does the rest for all sheets. Thanks for all the help!!