Auto Name Tab based on cell

[ryan_law2000]

Hey Everyone

I would like the tab at the bottom of the sheet to Automactially name = to cell C5
So if Cell C5 = "Ryan"
The tab at the bottom of the sheet will be named "Ryan"

Any ideas???

 

[lol.xls]

Re: what about dates?

Maybe

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address(False, False) = "C5" Then
    If IsDate(Target) Then
         Me.Name = Format(Target, "mm-dd-yy")
    Else: Me.Name = Target
    End If
End If
End Sub

lenze

Got it! Thanks a ton!

 

[lol.xls]

This is what i ended up using"

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
For Each ws In Worksheets
ActiveSheet.Name = Format(Range("H6").Value, "mm-dd-yy")
Next
End Sub

 

[VoG]

You could have used

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Sh.Name = Format(Range("H6").Value, "mm-dd-yy")
End Sub

 

[lenze]

This is what i ended up using"

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
For Each ws In Worksheets
ActiveSheet.Name = Format(Range("H6").Value, "mm-dd-yy")
Next
End Sub

If that works, go with it!! I would, however, put in some error traps. Also, this should not be a Change Event. Perhaps a regular macro. Something like

Sub Test()
On Error Resume Next
For Each ws In Worksheets
If IsDate(Range("H6") Then
      ws.Name = Format(Range("H6"), "mm-dd-yy")
Else: MsgBox ws.CodeName & " has an invalid entry"
End If
Next ws
End Sub

Or, make it an On_Open event

Private Sub Workbook_Open()
'Rest of code

HTH
lenze

 

[lol.xls]

so whenever you get sick of me just tell me to take a hike, but here's another one. now that the sheet naming is squared away, i have another problem. when i record a macro to "copy and create" a new sheet, it only copies the sheet that was used when recording the macro. how can i make the code below applicable to whatever sheet i'm currently working in?

Sub newrpt()
'
' newrpt Macro
'
'
Sheets("10-15-09").Select
Sheets("10-15-09").Copy Before:=Sheets(1)
Range("U6").Select
Selection.Copy
Range("H6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("I8:K8").Select
Application.CutCopyMode = False
End Sub

????

 

[VoG]

Try

Sub newrpt()
'
' newrpt Macro
'
'
ActiveSheet.Copy Before:=Sheets(1)
Range("U6").Copy
Range("H6").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

 

[Nimit]

In relevance to this particular question, I have for example:

ActiveSheet.Name = Range("A1").Value

plonked into the Worksheet_Activate() event. As a result, whenever I hit this particular worksheet, the renaming code gets executed and thereby triggering the whole worksheet formulae to recalculate. Does anyone has an alternate idea to get through this..?

 

[lenze]

I don't know why that would trigger a recalc, as you're not changing a cell, but maybe

Private Sub Worksheet_Activate()
On Error Resume Next
Application.EnableEvents = False
Me.Name=Range("$A$1")
Application.EnableEvents = True
End Sub

OR

Private Sub Worksheet_Activate()
On Error Resume Next
Application.Calculation = xlCalculationManual
Me.Name = Range("$A$1")
Application.Calculation = xlCalculationAutomatic
End Sub

lenze

 

[Nimit]

"Me" as in a pointer to the object itself...that is cool. I will check it out and post the feedback.

don't know why that would trigger a recalc

Because the worksheet contains a lot of formulae referencing other worksheets & workbooks too and I think the least reason could be the sheet name change in these external links contained within the formulae...

 

[Nimit]

Nope ! I tried that code of yours and still the same pathetic ongoing problem. thanks for your help though.