How can I link and label worksheet tabs to relative dates contained in each
worksheet?

You can't link them as you can't label a tab with a formula, but if you know
where the dates are for each tab then you can have code run through and name
each of the tabs based on those values.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Pat" wrote in message
...
How can I link and label worksheet tabs to relative dates contained in
each
worksheet?

Ken, can you explain further? I do not understand how to run code through.
I know where the dates are in each of the 52 worksheets I am using in my
workbook that compiles an annual report, and the dates are driven by formula
from a 53rd worksheet that compiles the info from the weekly worksheets.
I obviously have no programming skills.

"Ken Wright" wrote:

You can't link them as you can't label a tab with a formula, but if you know
where the dates are for each tab then you can have code run through and name
each of the tabs based on those values.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Pat" wrote in message
...
How can I link and label worksheet tabs to relative dates contained in
each
worksheet?

Where exactly are the dates that you want to name the sheets with. Give me
exact ranges, and is it one per sheet etc

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Pat" wrote in message
...
Ken, can you explain further? I do not understand how to run code
through.
I know where the dates are in each of the 52 worksheets I am using in my
workbook that compiles an annual report, and the dates are driven by
formula
from a 53rd worksheet that compiles the info from the weekly worksheets.
I obviously have no programming skills.

"Ken Wright" wrote:

You can't link them as you can't label a tab with a formula, but if you
know
where the dates are for each tab then you can have code run through and
name
each of the tabs based on those values.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

--------------------------------------------------------------------------
--

"Pat" wrote in message
...
How can I link and label worksheet tabs to relative dates contained in
each
worksheet?

My workbook has 53 worksheets, and worksheet #1 is a summary of the next 52
weekly worksheets.

Worksheet #1 (summary) cell B4 contains the formula =DATE(5,1,2) followed
by cell C4 formula =B4+7 , cell D4 =C4+7 , etc through cell BA4, which is
week 52

Worksheet #2 (week 1) cell B4 contains the formula ='2005 Summary'!$B$4
from the summary worksheet #1, followed by

Worksheet #3 (week 2) cell B4 ='2005 Summary'!$C$4 from the summary
worksheet #1, followed by 50 more worksheet linked back to the summary
worksheet.

I would like to label each worksheet tab with the date derived from the
formula in each respective cell B4.

Ken, does this give you the exact range information you need?

Thanks

"Ken Wright" wrote:

Where exactly are the dates that you want to name the sheets with. Give me
exact ranges, and is it one per sheet etc

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Pat" wrote in message
...
Ken, can you explain further? I do not understand how to run code
through.
I know where the dates are in each of the 52 worksheets I am using in my
workbook that compiles an annual report, and the dates are driven by
formula
from a 53rd worksheet that compiles the info from the weekly worksheets.
I obviously have no programming skills.

"Ken Wright" wrote:

You can't link them as you can't label a tab with a formula, but if you
know
where the dates are for each tab then you can have code run through and
name
each of the tabs based on those values.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

--------------------------------------------------------------------------
--

"Pat" wrote in message
...
How can I link and label worksheet tabs to relative dates contained in
each
worksheet?

Pat

If each B4 will be a unique date, and it looks like it should be........

Sub wsname()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Name = ws.Range("B4").Value
Next ws
End Sub


Gord Dibben Excel MVP

On Sun, 30 Jan 2005 09:15:06 -0800, Pat wrote:

My workbook has 53 worksheets, and worksheet #1 is a summary of the next 52
weekly worksheets.

Worksheet #1 (summary) cell B4 contains the formula =DATE(5,1,2) followed
by cell C4 formula =B4+7 , cell D4 =C4+7 , etc through cell BA4, which is
week 52

Worksheet #2 (week 1) cell B4 contains the formula ='2005 Summary'!$B$4
from the summary worksheet #1, followed by

Worksheet #3 (week 2) cell B4 ='2005 Summary'!$C$4 from the summary
worksheet #1, followed by 50 more worksheet linked back to the summary
worksheet.

I would like to label each worksheet tab with the date derived from the
formula in each respective cell B4.

Ken, does this give you the exact range information you need?

Thanks

"Ken Wright" wrote:

Where exactly are the dates that you want to name the sheets with. Give me
exact ranges, and is it one per sheet etc

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Pat" wrote in message
...
Ken, can you explain further? I do not understand how to run code
through.
I know where the dates are in each of the 52 worksheets I am using in my
workbook that compiles an annual report, and the dates are driven by
formula
from a 53rd worksheet that compiles the info from the weekly worksheets.
I obviously have no programming skills.

"Ken Wright" wrote:

You can't link them as you can't label a tab with a formula, but if you
know
where the dates are for each tab then you can have code run through and
name
each of the tabs based on those values.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

--------------------------------------------------------------------------
--

"Pat" wrote in message
...
How can I link and label worksheet tabs to relative dates contained in
each
worksheet?

No error checking in there but try this:-

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane
you need to search for
your workbook's name, and when you find it you may need to click on the + to
expand it. Within
that you should see the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook

If you have named your sheets then those names will appear in the brackets
above as opposed to
what you see at the moment in my note.

Right click on the where it says VBAProject(Your_Filename) and choose
'Insert Module' and it will now look like this

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook
Modules
Module1

Double click the VBAProject(Your_Filename) and then paste in the following
code starting at the Sub ShtNames() bit and finishing at the End Sub() bit.

Sub ShtNames()

Dim x As Long
Dim tn As String

For x = 2 To Sheets.Count
With Sheets(x)
tn = Format(.Range("B4").Value, "yyyy-mm-dd")
.Name = tn
End With
Next
End Sub

Then hit File / Close and return to Microsoft Excel and save the file. Now
just do Tools / Macro / Macros / ShtNames

If you want a different date format then just change the format in the code
(ie the "yyyy-mm-dd" bit) to whatever you want it to be.


If you then want to get rid of the macro, then do the following:-

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane
you need to search for
your workbook's name, and when you find it you may need to click on the + to
expand it. Within
that you should see the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
etc..........................
ThisWorkbook
Modules
Module1

Right click on the Module1 and select remove. When prompted with a question
re exporting, just
hit no. Then hit File / Close and return to Microsoft Excel and save the
file.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Pat" wrote in message
...
My workbook has 53 worksheets, and worksheet #1 is a summary of the next
52
weekly worksheets.

Worksheet #1 (summary) cell B4 contains the formula =DATE(5,1,2)
followed
by cell C4 formula =B4+7 , cell D4 =C4+7 , etc through cell BA4, which is
week 52

Worksheet #2 (week 1) cell B4 contains the formula ='2005 Summary'!$B$4
from the summary worksheet #1, followed by

Worksheet #3 (week 2) cell B4 ='2005 Summary'!$C$4 from the summary
worksheet #1, followed by 50 more worksheet linked back to the summary
worksheet.

I would like to label each worksheet tab with the date derived from the
formula in each respective cell B4.

Ken, does this give you the exact range information you need?

Thanks

"Ken Wright" wrote:

Where exactly are the dates that you want to name the sheets with. Give
me
exact ranges, and is it one per sheet etc

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

--------------------------------------------------------------------------
--

"Pat" wrote in message
...
Ken, can you explain further? I do not understand how to run code
through.
I know where the dates are in each of the 52 worksheets I am using in
my
workbook that compiles an annual report, and the dates are driven by
formula
from a 53rd worksheet that compiles the info from the weekly
worksheets.
I obviously have no programming skills.

"Ken Wright" wrote:

You can't link them as you can't label a tab with a formula, but if
you
know
where the dates are for each tab then you can have code run through
and
name
each of the tabs based on those values.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03



--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission
:-)


--------------------------------------------------------------------------
--

"Pat" wrote in message
...
How can I link and label worksheet tabs to relative dates
contained in
each
worksheet?

Grrrrrrrrrr

Where it says

Double click the VBAProject(Your_Filename)<<< and then paste in the
following
code starting at the Sub ShtNames() bit and finishing at the End Sub() bit.

read that as

Double click the Module1<<< bit and then paste in the following
code starting at the Sub ShtNames() bit and finishing at the End Sub() bit.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip

Thanks - works perfectly! This is the first macro I have ever applied.
Excellent coaching!

Thanks again!

"Pat" wrote:

How can I link and label worksheet tabs to relative dates contained in each
worksheet?

You're welcome. It's always easier when someone walks you through step by
step the first time. :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Pat" wrote in message
...
Thanks - works perfectly! This is the first macro I have ever applied.
Excellent coaching!

Thanks again!

"Pat" wrote:

How can I link and label worksheet tabs to relative dates contained in
each
worksheet?