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?
|