Macro to add new worksheet & reference the new worksheet name in the worksheet

[77winston]

Hi,
I hope someone could help me with a query I have.

I'm using a the below macro to create new worksheets to an workbook.

Sub CreateNewSheet()Dim sName As String
Sheets("Draw Data").Copy after:=ActiveSheet


sName = InputBox("Enter new sheet name")
On Error Resume Next
ActiveSheet.Name = sName
If Err <> 0 Then MsgBox "Name not valid"
Err.Clear
On Error GoTo 0
End Sub

This macro works fine, as an addition I want the sheet name that was entered to appear on cell B3 within the worksheet.
Could this be achieved?

Please advise.

Thanks.

 

[JoeMo]

try:
Sub CreateNewSheet()Dim sName As String
Sheets("Draw Data").Copy after:=ActiveSheet


sName = InputBox("Enter new sheet name")
if sName = "" then 'Cancel was clicked
application.displayalerts=false
activesheet.delete
exit sub
end if
On Error Resume Next
ActiveSheet.Name = sName
If Err <> 0 Then
MsgBox "Name not valid"
application.displayalerts=false
activesheet.delete
exit sub
end if
range("B3").Value = sName
application.displayalerts=true
End Sub

 

[77winston]

WOW. Brilliant! Thanks. That code did the trick.

 

[JoeMo]

WOW. Brilliant! Thanks. That code did the trick.

You are welcome - thanks for the reply.