Exit from Excel Macro from sub

BillZab 

Basically, you need the procedure to communicate the user's choice back to the "main sub". The good coding way to do this is to make a Function (i.e., not a Sub). The syntax (in VBA) to return a value from a function is to assign a value (whether a literal or from a variable) to the name of the function.

Suggested replacement:

Function PromptOnDuplicateFile() As String
'   This function prompts the user for what action to take when a duplicate
'   file is found; it returns a description of the user's choice.

    Dim Answer As VbMsgBoxResult
    
    Answer = MsgBox("Duplicate file found. Overwrite Y/N?" _
        , vbExclamation + vbYesNo)  '[could use vbYesNoCancel]
    If Answer = vbYes Then
        PromptOnDuplicateFile = "Overwrite"
        Exit Function
    ElseIf Answer = vbNo Then
        'ActiveWorkbook.Close  '[This is not central to what this function should do - prompt the user and return the result.  It would better be performed from the "main sub".]
        PromptOnDuplicateFile = "Retry"
        Exit Function
    'ElseIf Answer = vbCancel Then
    '    PromptOnDuplicateFile = "QuitTrying"
    '    Exit Function
    End If

End Function

Note the "As String" on the Function statement, and the assignment statements of string values. (Returning a String rather than a Boolean allows you to change it readily to allow other options, such as "QuitTrying". That's not the most efficient code, but it makes the intent clear.  And using VbMsgBoxResult rather than Integer allows VBE's Intellisense to help you code.)

The invoking code (in the "main sub") should (preferably capture the return value from this function into a variable, and) then execute one of the different pieces of code, depending on the return value.

    Dim NextAction As String
    NextAction = PromptOnDuplicateFile()
    If NextAction = "Overwrite" Then
        '[code for overwrite]
    Else
        ActiveWorkbook.Close
        If NextAction = "Retry" Then
            '[additional code for retry; possibly a GoTo <label>]
        ElseIf NextAction = "QuitTrying" Then
            '[code for that]
        End If
    End If