How to link Excel worksheet tab names to dates in each worksheet?

[Pat]

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

 

[Ken Wright]

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.

 

[Pat]

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]

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

 

[Pat]

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

 

[Gord Dibben]

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

 

[Ken Wright]

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.

 

[Ken Wright]

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.

 

[Pat]

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

Thanks again!

 

[Ken Wright]

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