If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Naming a worksheet tab from a reference cell
Hi All,
Using Excel 2007 Can I name a worksheet tab from a cell contained within the worksheet. I want to name a worksheet from the date contained in say A1 = 30/06/2009(dd/mm/yyyy) so the tab would be called 'June' from the moonth number in A1. If so could someone give me either the function or the code to do this. If it is code could also detail how to apply it,as I am not that familiar with code. Many thanks for taking the time out to respond. FinanceGuru -- Wales - the land of fire breathing dragons and rugby playing wizards. |
#2
|
|||
|
|||
Naming a worksheet tab from a reference cell
You will need to get a formula to extract the month from your date. Sheet
names can not be blank or contain certain symbols: / \ [ ] * ?. So, in A2, place this formula: =TEXT(A1,"mmmm") You'll then need to use code. This will set your sheet name to the value of A1 when sheet is activated. Right click on the sheet tab, view code, paste this in: Private Sub Worksheet_Activate() Activesheet.Name = Range("A2").Value End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Finance Guru" wrote: Hi All, Using Excel 2007 Can I name a worksheet tab from a cell contained within the worksheet. I want to name a worksheet from the date contained in say A1 = 30/06/2009(dd/mm/yyyy) so the tab would be called 'June' from the moonth number in A1. If so could someone give me either the function or the code to do this. If it is code could also detail how to apply it,as I am not that familiar with code. Many thanks for taking the time out to respond. FinanceGuru -- Wales - the land of fire breathing dragons and rugby playing wizards. |
#3
|
|||
|
|||
Naming a worksheet tab from a reference cell
Improved code:
Rather than use a formula in worksheet, can do it directly via code with this: Private Sub Worksheet_Activate() ActiveSheet.Name = WorksheetFunction.Text(Range("A1").Value, "mmmm") End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Finance Guru" wrote: Hi All, Using Excel 2007 Can I name a worksheet tab from a cell contained within the worksheet. I want to name a worksheet from the date contained in say A1 = 30/06/2009(dd/mm/yyyy) so the tab would be called 'June' from the moonth number in A1. If so could someone give me either the function or the code to do this. If it is code could also detail how to apply it,as I am not that familiar with code. Many thanks for taking the time out to respond. FinanceGuru -- Wales - the land of fire breathing dragons and rugby playing wizards. |
Thread Tools | |
Display Modes | |
|
|