VBA Error handler called regardless of exit sub statement

[Noodleski]

Hi all,

I've been lurking and browsing this forum for quite some time. Thanks for the excellent advice so far. But this time, I've encountered something that I haven't been able to solve, despite looking at error handling threads.

The setup is simple:
Copy a sheet, disconnect the formulae from the source data and save it as a kind of archive for future reference.

So I came up with this (not accustomed to writing VBA code, not an educated IT guy. Simple trial and error so be gentle)

On Error GoTo handler

Dim MySheetName As String
MySheetName = Range("name").Value

    Sheets("sourcesheet").Copy After:=Sheets("sourcesheet")
    ActiveSheet.Name = MySheetName
    Range("A3:F23").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
Exit Sub

handler:

Application.DisplayAlerts = False
Sheets("Sourcesheet (2)").Delete
Application.DisplayAlerts = True
MsgBox "Sheet with same name already exists. No new sheet created"

The issue I have is that the Exit sub - statement right before the handler seems to be overlooked, because the macro is triggering the handler anyhow, ironically throwing an error that the sheet it needs to delete doesn't exist.

With my petty skills, this has me stumped so any insight is more than welcome!

 

[RoryA]

I suspect your clipboard has been cleared by copying the sheet. You'll need to recopy whatever was originally there before pasting.

 

[Noodleski]

Oh dear...
Just figured it out.

I left out this bit

    Range("A3:F23").Select
    [U][B]Selection.Copy[/B][/U]
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

And as a result, it slipped into the error handler.

And now my mistake's is visible to all.
But at least, I got around to registering here, so it's not all bad.