VBA Code for changing Tab names

Question

I have 52 Sheets & each sheet represents each week ,example: 28-Jan

What I would like to know is it possible to have a code or formula to change Tab these Names

Each week C3 will have the Date needed for each Tab Name if this is possible

If I enter the Date in 1st sheet C3 The other 51 sheets will be updated in cells C3 from 1st sheet using the example date above

Answer 1

The code was written on the assumption that the date would be in cell C3 but as you are inserting the date in D3 of the controlling sheet, the code in the following line requires you to test for a change in D3; not C3.

Edit the following line

If Target.Address = "$C$3" Then

Should now be

If Target.Address = "$D$3" Then

The code tests which cell is being altered otherwise it would run every time any cell on the worksheet is changed.

I am assuming the the date in the other worksheets to be renamed is still in C3.

Answer 2

This is the Code I am using But again nothing is happening

I have this code in Sheet named "Seniority List" & this is where the controlling Dates are set up "D3" To setup work book

When I change the Date in Sheet named Seniority List D3 nothing happens for rest of work book The controlling Date in D3 is 28/01/2013 ,      Other 52 sheets will be "dd-mmm"

All sheets are already named with Dates Example : 28-Jan & so on ., & other 4 sheets are named as per in Code below

What I need to happen is when a NEW YEAR BEGINS I WILL CHANGE DATE IN  SHEET NAMED Seniority List D3 & THE 52 SHEETS C3 WILL FOLLOW & NAMES FOR THESE SHEETS WILL CHANGE hOPEFULLY USING CODE BELOW IF THIS WORKS

Private Sub Worksheet_Change(ByVal Target As Range)

    'Following line assumes that date is changed in cell C3

     If Target.Address = "$C$3" Then

         Dim ws As Worksheet

         Dim i As Long

        'Rename sheets with temporary name so no possibility of name clash

         For Each ws In Worksheets

             Select Case ws.Name

                 'Edit following line with list of sheets to exclude

                 Case "Seniority List", "Mandatory OverTime", "Seniority List (2)", "Mandatory OverTime (2)"

                     'Do nothing with these sheets

                 Case Else

                     i = i + 1

                     'Select a name in following line that is unlikely to be used elsewhere

                     ws.Name = "Hutch" & i

             End Select

         Next ws

        For Each ws In Worksheets

             Select Case ws.Name

                 'Edit following line with list of sheets to exclude

                 Case "Seniority List", "Mandatory OverTime", "Seniority List (2)", "Mandatory OverTime (2)"

                     'Do nothing with these sheets

                 Case Else

                     If IsDate(ws.Range("C3")) Then

                         ws.Name = Format(ws.Range("C3"), "dd-mmm")

                     Else

                         'MsgBox included in case of error with date in cell C3

                         MsgBox "Worksheet " & ws.Name & " does not have valid date in cell C3"

                     End If

             End Select

         Next ws

         Set ws = Nothing

     End If

 End Sub

Answer 3

I am interpreting this as you change the date in the first sheet of the 52 sheets and then the dates in the other 51 sheets get changed by adding 7 to the date in the previous sheet. Four sheets are to be excluded from the name change.

Something to remember when re-naming sheets based on Formulas is to ensure that there is no possibility of a clash with sheet names. For instance if you changed the date in the controlling sheet by one week then there will already be sheets named with dates that are to become the new sheet names for other sheets and the code will fail. To overcome this, rename all of the sheets to temporary names before renaming to the new names.

The following code is designed to go into the code module of the controlling worksheet where you change the date and then each time the date is changed, the code will auotmatically execute.

To install the code:

Right Click the tab name of the controlling worksheet and select View Code.

Copy the code below into the VBA editor.

Note the comments and edit the code in the Case statement to list the sheets that are not to be included for the name change. Ensure you get the sheet names correct with no typos.

Private Sub Worksheet_Change(ByVal Target As Range)

    'Following line assumes that date is changed in cell C3

    If Target.Address = "$C$3" Then

        Dim ws As Worksheet

        Dim i As Long

        'Rename sheets with temporary name so no possibility of name clash

        For Each ws In Worksheets

            Select Case ws.Name

                'Edit following line with list of sheets to exclude

                Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"

                    'Do nothing with these sheets

                Case Else

                    i = i + 1

                    'Select a name in following line that is unlikely to be used elsewhere

                    ws.Name = "OssieMac" & i

            End Select

        Next ws

        For Each ws In Worksheets

            Select Case ws.Name

                'Edit following line with list of sheets to exclude

                Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"

                    'Do nothing with these sheets

                Case Else

                    If IsDate(ws.Range("C3")) Then

                        ws.Name = Format(ws.Range("C3"), "dd-mmm")

                    Else

                        'MsgBox included in case of error with date in cell C3

                        MsgBox "Worksheet " & ws.Name & " does not have valid date in cell C3"

                    End If

            End Select

        Next ws

        Set ws = Nothing

    End If

End Sub

Answer 4

I tried this code but no luck "I got nothing"

Maybe I can make it more clearer of what I want to happen or what you need to make this happen

I actually have 56 sheets but only 52 sheets will be renamed using cells C3 "Dates"

I have a starter Date & when this Date is modified , C3 in other 52 sheets will follow that will = 52 weeks

What I would like to happen is whatever date is in C3 for the 52 sheets is to have the Sheet Tab names renamed to = C3 cells in all 52 sheets

The Format for cells C3 Dates is dd-mmm & Tab names will hopefully be the same

I hope this will help to make this possible

Answer 5

... If I enter the Date in 1st sheet C3 The other 51 sheets will be updated in cells C3 from 1st sheet using the example date above

Tap Alt+F11 and when the VBE opens, immediately use the pull-down menus to Insert, Module. Paste the following into the new pane titled something like Book1 - Module1 (Code):

Sub mcrWeekly_Sheet_Names()

    Dim t As Long

    If IsDate(Sheets(1).Range("C3")) Then

        For t = 1 To 52

            If t > Sheets.Count Then _

                Sheets.Add After:=Sheets(Sheets.Count)

            Sheets(t).Name = Format(DateAdd("ww", t - 1, _

              Sheets(1).Range("C3")), "dd-mmm")

        Next t

    End If

End Sub

Tap Alt+Q to return to your worksheet and tap Alt+F8 to open the Macros dialog and Run the code.

Note that if you do not have 52 worksheets, they will be created for you.