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!

 

[bnspool]

Yup - sure did! Do i need to start over from scratch?

 

[VoG]

I don't think so. I don't really understand the purpose of that event code except to give an error message if you rename a sheet.

 

[bnspool]

hmm - OK. So now what? I don't have any other code in place. This code did do what I wanted, only it kept giving me that message box when entering data to other areas of the sheet.

 

[bnspool]

Did i misunderstand? i deleted the whole code. You probably meant for me to delete only the portion containing pesky message...

 

[VoG]

If you no longer get the error message what is the problem?

 

[VoG]

Did i misunderstand? i deleted the whole code. You probably meant for me to delete only the portion containing pesky message...

Yes, exactly. Only the event code.

 

[bnspool]

It works correctly - now that I correctly interpreted your original advice. Big thanks!

I have one more feature I want to build in before I give this spreadsheet to my HR rep to field test. Should I start a new thread for that?

 

[VoG]

Yes, please start a new thread.

 

[bnspool]

Done. Thanks again.