Naming worksheets tabs from another cell reference
Hello All,
Using Excel 2007. Question - Can you name a worksheet tab from a Cell within the worksheet. In a1 I have the date 30/06/09 (dd/mm/yyyy) and I would like the worsheet to be called "June" picked up from the mm in the date. If so could someone give me the function / code ( and how to apply the code as well ) as I am a fairly inexpert user of excel. Many thanks for taking the time out to reply FinanceGuru -- Wales - the land of fire breathing dragons and rugby playing wizards. |
Naming worksheets tabs from another cell reference
You didn't say when you wanted this sheet name change to take place.
Assuming that you want the name change to take place when a date is entered in A1, this little macro will do that. Place this macro in the sheet module of the sheet in which you want this to work. You can access that module by right-clicking on the sheet tab and selecting View Code. Paste this macro into that module. "X" out of the module to return to your sheet. Note that this macro will work with only that one sheet. Come back if you want it to work on other sheets as well. If so, tell us if you want it to work with ALL of the sheets, or give us the names of the excluded sheets. Also note that if the entry in A1 is NOT a valid date, the sheet name will not be changed. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("A1")) Is Nothing Then If IsDate(Range("A1")) Then _ ActiveSheet.Name = Format(Range("A1"), "mmmm") End If End Sub "Finance Guru" wrote in message ... Hello All, Using Excel 2007. Question - Can you name a worksheet tab from a Cell within the worksheet. In a1 I have the date 30/06/09 (dd/mm/yyyy) and I would like the worsheet to be called "June" picked up from the mm in the date. If so could someone give me the function / code ( and how to apply the code as well ) as I am a fairly inexpert user of excel. Many thanks for taking the time out to reply FinanceGuru -- Wales - the land of fire breathing dragons and rugby playing wizards. |
Naming worksheets tabs from another cell reference
How would you modify the code to pull some text from a cell to become the
worksheet name. I would like to pull the job number (usually written as J####). Thanks "Otto Moehrbach" wrote: You didn't say when you wanted this sheet name change to take place. Assuming that you want the name change to take place when a date is entered in A1, this little macro will do that. Place this macro in the sheet module of the sheet in which you want this to work. You can access that module by right-clicking on the sheet tab and selecting View Code. Paste this macro into that module. "X" out of the module to return to your sheet. Note that this macro will work with only that one sheet. Come back if you want it to work on other sheets as well. If so, tell us if you want it to work with ALL of the sheets, or give us the names of the excluded sheets. Also note that if the entry in A1 is NOT a valid date, the sheet name will not be changed. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("A1")) Is Nothing Then If IsDate(Range("A1")) Then _ ActiveSheet.Name = Format(Range("A1"), "mmmm") End If End Sub "Finance Guru" wrote in message ... Hello All, Using Excel 2007. Question - Can you name a worksheet tab from a Cell within the worksheet. In a1 I have the date 30/06/09 (dd/mm/yyyy) and I would like the worsheet to be called "June" picked up from the mm in the date. If so could someone give me the function / code ( and how to apply the code as well ) as I am a fairly inexpert user of excel. Many thanks for taking the time out to reply FinanceGuru -- Wales - the land of fire breathing dragons and rugby playing wizards. |
All times are GMT +1. The time now is 03:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com