Creating a Copy worksheet error when renaming it

[NetWave128]

Here is my code:

Public Sub NewInterface()
Dim NewSheet As Worksheet

On Error GoTo ErrorHandler

Set NewSheet = ThisWorkbook.Worksheets("Interfac
Master").Copy(after:=ThisWorkbook.Worksheets("Interface Master"))
NewSheet.Name = "New Interface Sheet"

Exit Sub '<-- if everything runs error-free, you will exit here
BEFORE the error handler.

ErrorHandler:
MsgBox "Sorry, an error occurred." & vbCrLf & "Number: " & Err.Numbe
& vbCrLf & "Description: " & Err.Description, vbCritical, "Error"

End Su

Attachment filename: error.gif
Download attachment: http://www.excelforum.com/attachment.php?postid=46849

 

[Tom Ogilvy]

the copy method for a worksheet doesn't not return anything. You can't use
(it doesn't return a reference to the copy of the sheet).

Set NewSheet = ThisWorkbook.Worksheets("Interface
Master").Copy(after:=ThisWorkbook.Worksheets("Interface Master"))

Instead:

ThisWorkbook.Worksheets("Interface Master").Copy _
after:=ThisWorkbook.Worksheets("Interface Master")
set NewSheet = Activesheet

 

[NetWave128]

Thank You that works no