Tab Rename

[bnspool]

I am brand new to Excel forums and a rookie macro creator. I am looking for a simple code to rename each new tab to match a selected cell input (in this case cell B9).

What I have works, but triggers this message box every time inputs are made elsewhere on the sheet:

"The name (tab name) is not a valid Sheet name"

Here's the code I'm using:

Sub SheetCreateDated()
Dim newsht As Worksheet, ws As Worksheet
Dim ivalue As String
ivalue = Sheets("Claimant1").Range("B9").Value
For Each ws In Worksheets
If ws.Name = ivalue Then
MsgBox ("There is already a sheet with the name " & "*" & ivalue & "*")
Exit Sub
End If
Next ws
Set newsht = Worksheets.Add
With newsht
.Move After:=Sheets(Sheets.Count)
.Name = ivalue
End With
Sheets("Claimant1").UsedRange.Copy Destination:=newsht.Range("B9")
newsht.Range("A3").Value = ivalue
End Sub

Thanks in advance for any help you can provide!

 

[VoG]

What do you have in B9?

 

[bnspool]

B9 will contain an employee name - a different name for each new tab created.

 

[bnspool]

B9 will contain an employee name - a different name for each new tab created.
<!-- / message -->

 

[VoG]

That should work provided the employee's name is less than 32 characters long.

Is there anything else going on like event code?

 

[Trevor G]

B9 will contain an employee name - a different name for each new tab created.
<!-- / message -->

Can you show a sample of the cell contents?

 

[bnspool]

Yes. It works and meets the character limits. However, I still get a message box that says the new name is not a valid sheet name. This message box appears after any entry anywhere else on the sheet.

 

[VoG]

Right click the sheet tab, select View Code. Is there code there? If so please post it.

 

[bnspool]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strNew As String, strOld As String
If Target.Cells.Count > 1 Then Exit Sub

With Range("B9")
On Error Resume Next
If Not (Application.Intersect(Target, .DirectPrecedents) Is Nothing) Then
strOld = Me.Name
strNew = CStr(.Value)
Me.Name = strNew

If Me.Name = strOld Then
MsgBox "The name " & strNew & " is not a valid Sheet name"
End If
End If
On Error GoTo 0
End With

End Sub

 

[VoG]

If you delete that code the error message will go away