VB Rename TABs based on a List

[godzilla65]

Hi All,

Rename TABS

I have a workbook called "Brisbane Movements.xls"

In cell "C2:H2", merged cells within each worksheet. The date format is currently displayed as 1/04/2011

I basically want to use a Command Button to Rename the Tabs based on the Cell Reference
in Cell "C2:H2".

There is 13 worksheets that I rename every day ( time consuming )

I would like the Tabs to be renamed in the following format if for example the date is 1/04/2011 the Tab for that worksheet would be renamed and displayed as FRI 01 being the Day of the week and the Date.

Any help would be appreciated with this code.

Thanks Eric

 

[RSXchin]

What are all of your sheets going to be called? And is there a reason you don't just delete the first sheet and create a new sheet at the end?

 

[godzilla65]

Howdy There

What are all of your sheets going to be called?

I wish the sheets/tabs to be called eg: FRI 01 which is derived from the formated Date within the Cells in worksheet C2:H2, in C2:H2 its simply the date eg: 01/04/2011
so the idea is the commandbutton renames that worksheet to FRI 01 which is the format of ddd dd when macro performed

And is there a reason you don't just delete the first sheet and create a new sheet at the end?

Each worksheet is a days roster, and I have 13 days worth of rosters for which I manually rename the tabs everyday to match the Day of Week and the day of the Month eg FRI 01

Hope the above assists Cheers Eric

 

[RSXchin]

are there more sheets before "FRI 01"?

 

[RSXchin]

If the very first tab is a roster AND the first sheet is today's date, then you can use this

Sub rename_tabs_to_date()
    For i = 1 To 13
        Sheets(i).Name = Format(Now - 1 + i, "DDD DD")
    Next i
End Sub

 

[RSXchin]

crap... I just realized I didn't make the code do what you wanted it to do. I'll give you the right on in a few minutes.

 

[godzilla65]

Sorry I was a little confused and am a very basic level VB Novice.

I copied the code into my Command Button as below and nothing happened

Private Sub CommandButton1_Click()
Sub rename_tabs_to_date()
For i = 1 To 13
Sheets(i).Name = Format(Now - 1 + i, "DDD DD")
Next i
End Sub
End Sub

I created a new worksheet and called it Todays Date, I was not sure if I was meant to add or reference the date within a cell eg A1 in the Todays Date worksheet or not but I could not get any function from the code, I even moved the first roster/tab to that of the 1st roster in the workbook and tried ?

Little lost

 

[RSXchin]

here it is... sorry about that

Private Sub CommandButton1_Click()
    For i = 1 To 13
        Sheets(i).Name = Format(Sheets(i).Range("C2").Value, "DDD DD")
    Next i
End Sub

 

[tusharm]

If I understand you correctly...

Option Explicit

Sub renameSheets()
    Dim WS As Worksheet
    For Each WS In ActiveWorkbook.Worksheets
        With WS.Range("c2")
        If IsDate(.Value) Then _
            WS.Name = Format(.Value, "DDD DD")
            End With
        Next WS
    End Sub

Hi All,

Rename TABS

I have a workbook called "Brisbane Movements.xls"

In cell "C2:H2", merged cells within each worksheet. The date format is currently displayed as 1/04/2011

I basically want to use a Command Button to Rename the Tabs based on the Cell Reference
in Cell "C2:H2".

There is 13 worksheets that I rename every day ( time consuming )

I would like the Tabs to be renamed in the following format if for example the date is 1/04/2011 the Tab for that worksheet would be renamed and displayed as FRI 01 being the Day of the week and the Date.

Any help would be appreciated with this code.

Thanks Eric