Time sheet with 52 tabs
 

I have a time sheet with 52 tabs, each tab name is the date of that
particular week. I would like to automate the naming of the last 51 tabs so
they would calculate the date based on the first tab's date.

Anyone have any ideas on this?
Thanks in advance,
Dave

Time sheet with 52 tabs
 

Sub LabelSheets()
Dim sh as Object
Dim dt as Date
for each sh in ActiveWorkbook.Sheets
if sh.Index = 1 then
dt = cDate(sh.name)
else
sh.Name = format(dt +7,"mm-dd-yy")
dt = dt + 7
end if
Next
End Sub

--
Regards,
Tom Ogilvy


"David Parks" wrote in message
...
I have a time sheet with 52 tabs, each tab name is the date of that
particular week. I would like to automate the naming of the last 51 tabs
so
they would calculate the date based on the first tab's date.

Anyone have any ideas on this?
Thanks in advance,
Dave

Time sheet with 52 tabs
 

Thanks a lot Tom. It works perfectly. How can I cause the macro to run when
the first tab name is edited?
Cheers,
Dave

"Tom Ogilvy" wrote in message
...
Sub LabelSheets()
Dim sh as Object
Dim dt as Date
for each sh in ActiveWorkbook.Sheets
if sh.Index = 1 then
dt = cDate(sh.name)
else
sh.Name = format(dt +7,"mm-dd-yy")
dt = dt + 7
end if
Next
End Sub

--
Regards,
Tom Ogilvy


"David Parks" wrote in message
...
I have a time sheet with 52 tabs, each tab name is the date of that
particular week. I would like to automate the naming of the last 51 tabs
so
they would calculate the date based on the first tab's date.

Anyone have any ideas on this?
Thanks in advance,
Dave

Time sheet with 52 tabs
 

Unfortunately, there is no event raised when a sheet tab is
renamed. Thus, the process cannot be automated.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"David Parks" wrote in message
...
Thanks a lot Tom. It works perfectly. How can I cause the macro
to run when
the first tab name is edited?
Cheers,
Dave

"Tom Ogilvy" wrote in message
...
Sub LabelSheets()
Dim sh as Object
Dim dt as Date
for each sh in ActiveWorkbook.Sheets
if sh.Index = 1 then
dt = cDate(sh.name)
else
sh.Name = format(dt +7,"mm-dd-yy")
dt = dt + 7
end if
Next
End Sub

--
Regards,
Tom Ogilvy


"David Parks" wrote in message
...
I have a time sheet with 52 tabs, each tab name is the date
of that
particular week. I would like to automate the naming of the
last 51 tabs
so
they would calculate the date based on the first tab's date.

Anyone have any ideas on this?
Thanks in advance,
Dave

Time sheet with 52 tabs
 

David,

You could copy this into the code page of the first worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False

If Target.Address = Range("TabName").Address Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

Thus, whenever the value in the range "TabName" is changed, the worksheet
name will change to that value. So, if TabName is A1 and you type 01-07-05
into it, the worksheet name will change to 01-07-05. If you then run Tom's
code after that...

.... I hope - well, it IS Friday afternoon!

Have a good weekend

Pete

"David Parks" wrote:

Thanks a lot Tom. It works perfectly. How can I cause the macro to run when
the first tab name is edited?
Cheers,
Dave

"Tom Ogilvy" wrote in message
...
Sub LabelSheets()
Dim sh as Object
Dim dt as Date
for each sh in ActiveWorkbook.Sheets
if sh.Index = 1 then
dt = cDate(sh.name)
else
sh.Name = format(dt +7,"mm-dd-yy")
dt = dt + 7
end if
Next
End Sub

--
Regards,
Tom Ogilvy


"David Parks" wrote in message
...
I have a time sheet with 52 tabs, each tab name is the date of that
particular week. I would like to automate the naming of the last 51 tabs
so
they would calculate the date based on the first tab's date.

Anyone have any ideas on this?
Thanks in advance,
Dave

Time sheet with 52 tabs
 

Thanks everyone,
Peter, you're right, it is Friday. You have all given me some good ideas.
I'll work on it over the week end.
Thanks again,
Dave
"Peter Rooney" wrote in message
...
David,

You could copy this into the code page of the first worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False

If Target.Address = Range("TabName").Address Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

Thus, whenever the value in the range "TabName" is changed, the worksheet
name will change to that value. So, if TabName is A1 and you type 01-07-05
into it, the worksheet name will change to 01-07-05. If you then run Tom's
code after that...

... I hope - well, it IS Friday afternoon!

Have a good weekend

Pete

"David Parks" wrote:

Thanks a lot Tom. It works perfectly. How can I cause the macro to run
when
the first tab name is edited?
Cheers,
Dave

"Tom Ogilvy" wrote in message
...
Sub LabelSheets()
Dim sh as Object
Dim dt as Date
for each sh in ActiveWorkbook.Sheets
if sh.Index = 1 then
dt = cDate(sh.name)
else
sh.Name = format(dt +7,"mm-dd-yy")
dt = dt + 7
end if
Next
End Sub

--
Regards,
Tom Ogilvy


"David Parks" wrote in message
...
I have a time sheet with 52 tabs, each tab name is the date of that
particular week. I would like to automate the naming of the last 51
tabs
so
they would calculate the date based on the first tab's date.

Anyone have any ideas on this?
Thanks in advance,
Dave

Time sheet with 52 tabs
 

David,

I think this will do what you want (even though it is Friday!)
Paste it all into the Code sheet of the worksheet containing the reference
to "TabName"

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False

If Target.Address = Range("TabName").Address Then
'Me.Name = Format(Target.Value)
Me.Name = Format(Year(Target.Value), "0000") & "-" & _
Format(Month(Target.Value), "00") & "-" & _
Format(Day(Target.Value), "00")

End If

LabelSheets

ws_exit:
Application.EnableEvents = True

End Sub

Sub LabelSheets()
Dim sh As Object
Dim dt As Date
For Each sh In ActiveWorkbook.Sheets
If sh.Index = 1 Then
dt = CDate(sh.Name)
Else
sh.Name = Format(dt + 7, "yyyy-mm-dd")
dt = dt + 7
End If
Next
End Sub

Have a good weekend

Pete


"David Parks" wrote:

Thanks everyone,
Peter, you're right, it is Friday. You have all given me some good ideas.
I'll work on it over the week end.
Thanks again,
Dave
"Peter Rooney" wrote in message
...
David,

You could copy this into the code page of the first worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False

If Target.Address = Range("TabName").Address Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

Thus, whenever the value in the range "TabName" is changed, the worksheet
name will change to that value. So, if TabName is A1 and you type 01-07-05
into it, the worksheet name will change to 01-07-05. If you then run Tom's
code after that...

... I hope - well, it IS Friday afternoon!

Have a good weekend

Pete

"David Parks" wrote:

Thanks a lot Tom. It works perfectly. How can I cause the macro to run
when
the first tab name is edited?
Cheers,
Dave

"Tom Ogilvy" wrote in message
...
Sub LabelSheets()
Dim sh as Object
Dim dt as Date
for each sh in ActiveWorkbook.Sheets
if sh.Index = 1 then
dt = cDate(sh.name)
else
sh.Name = format(dt +7,"mm-dd-yy")
dt = dt + 7
end if
Next
End Sub

--
Regards,
Tom Ogilvy


"David Parks" wrote in message
...
I have a time sheet with 52 tabs, each tab name is the date of that
particular week. I would like to automate the naming of the last 51
tabs
so
they would calculate the date based on the first tab's date.

Anyone have any ideas on this?
Thanks in advance,
Dave