P
Pat
How can I link and label worksheet tabs to relative dates contained in each
worksheet?
worksheet?
K
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.
where the dates are for each tab then you can have code run through and name
each of the tabs based on those values.
P
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.
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.
K
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
exact ranges, and is it one per sheet etc
P
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
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
G
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
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
K
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.
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.
K
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.
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.
P
Pat
Thanks - works perfectly! This is the first macro I have ever applied.
Excellent coaching!
Thanks again!
Excellent coaching!
Thanks again!
K
Ken Wright
You're welcome. It's always easier when someone walks you through step by
step the first time.
step the first time.