Setting Tab Names To Dates In Spreadsheet
 

Yes, the tab name is based on the date. Whereas the date displays in the
spreadsheet as 01/01/06 and then on word within the other spreadsheets by
adding one to the previous spreadsheet, it displays as 01-01-06 and so forth
in the tabs.

"Gary''s Student" wrote:

Is the tab name in any way based upon the date in A1? Remember that a tab
name cannot have a / in it.
--
Gary''s Student


"Chris" wrote:

I have a large workbook (150 sheets). In Sheet 1, Cell A1, I have the first
date (example: 01/01/06). I have Sheet 2, Cell A1 set to take the result of
Sheet 1, Cell A1 and add 1 to it to get 01/02/06, Sheet 3 adds 1 to sheet 2's
to get 01/03/06 and so on.

How can I set up the sheet tabs to automatically rename themselves when I
change the date in Sheet 1, Cell A1. I want it to change all the tabs
without having to go into each one and pressing enter or double-clicking them
to make the tabs change.

Setting Tab Names To Dates In Spreadsheet
 

Then we will use two steps, one in the worksheets and one in VBA:


1. In the worksheets, put any date in A1 of the first sheet
In the second sheet in A1 put =Sheet1!A1+1
Fill in A1 in each sheet in the same way
Now each A1 is one day later than the previous and all will change if
you change A1 in Sheet1


2. Enter the following macro:
Sub fixtab()
Dim w As Worksheet
Dim s As String
For Each w In ActiveWorkbook.Worksheets
s = w.Cells(1, 1).Value
s = Replace(s, "/", "-")
MsgBox (s)
w.Name = s
Next w
End Sub

3. First update the date in A1 in Sheet1 (all the other A1's will follow suit)
Then run the macro.
--
Gary''s Student


"Chris" wrote:

Yes, the tab name is based on the date. Whereas the date displays in the
spreadsheet as 01/01/06 and then on word within the other spreadsheets by
adding one to the previous spreadsheet, it displays as 01-01-06 and so forth
in the tabs.

"Gary''s Student" wrote:

Is the tab name in any way based upon the date in A1? Remember that a tab
name cannot have a / in it.
--
Gary''s Student


"Chris" wrote:

I have a large workbook (150 sheets). In Sheet 1, Cell A1, I have the first
date (example: 01/01/06). I have Sheet 2, Cell A1 set to take the result of
Sheet 1, Cell A1 and add 1 to it to get 01/02/06, Sheet 3 adds 1 to sheet 2's
to get 01/03/06 and so on.

How can I set up the sheet tabs to automatically rename themselves when I
change the date in Sheet 1, Cell A1. I want it to change all the tabs
without having to go into each one and pressing enter or double-clicking them
to make the tabs change.