Exit Sub Only if Sheet Not Found

[jacobrcotton]

Hi All,

I've never been let down here, and today will be no exception.

The Goal:
I have a macro running that will Save a workbook, Save As a workbook with a unique file name in the same path, then roll through each worksheet to copy & paste values, and then roll through each worksheet to prompt the user if they wish to delete said worksheet. It then finds the "Experience" worksheet and activates it so that when opened next, it opens to the "Experience" tab in cell A1. If a user accidentally deletes the "Experience" tab, i wish for the macro to prompt a MsgBox that states something to the tune of "Experience tab deleted", and then exit the Sub

The Problem:
Everything is working until the On Error GoTo ExitSub code.

Any assistance would be helpful. Thank you so much!

Sub ReadyForClient()


    Dim relativePath As String


    Select Case MsgBox("You Can't Undo This Action. " & "Save Workbook First?", vbYesNoCancel, "Alert")
        Case Is = vbYes
            ThisWorkbook.Save
            relativePath = ThisWorkbook.Worksheets("Documentation").Range("B1").Value & ThisWorkbook.Worksheets("Documentation").Range("B2").Value & "_cc.xlsm"
            ThisWorkbook.SaveAs Filename:=relativePath
        Case Is = vbCancel
            Exit Sub
    End Select


    Call UnhideAllSheets
    Call CopyPasteValuesAllSheets
    Call DeleteUnnecessaryTabs
    Call FindHomeAllSheets


    On Error GoTo ExitSub
    ThisWorkbook.Worksheets("Experience").Activate
    
ExitSub:
    MsgBox "Experience tab was deleted. Please review."
    Exit Sub


End Sub

 

[footoo]

Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets("Experience")
If Err <> 0 Then
    MsgBox "Experience tab was deleted. Please review."
    Exit Sub
Else
    Sheets("Experience").Activate
End If

 

[shknbk2]

The Exit Sub should come before the ExitSub: line. In your code, the MsgBox line will always be executed even if there was no error. Putting Exit Sub before the ExitSub: line skips the MsgBox if the activation was successful.

Also, the closeness of ExitSub with Exit Sub could be confusing. You might consider changing ExitSub to something else.

    On Error GoTo ExitSub
    ThisWorkbook.Worksheets("Experience").Activate
    Exit Sub
ExitSub:
    MsgBox "Experience tab was deleted. Please review."
End Sub

 

[jacobrcotton]

shknbk2, this worked perfectly. and yes, i think you are correct. I will change the same of "ExitSub" to something more distinct. Thanks so much!