Naming worksheets tabs from another cell reference

[Finance Guru]

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

 

[Otto Moehrbach]

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

 

[Devo]

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

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