Auto save Excel file on exit

[Del_Piero_3]

Hey guys, need some help. I use the following code to auto save a file when I click the x/close on the Excel application. The filename that is saved under is a value from a cell ("E12") in that file.

Sub Auto_Close()
On Error GoTo errtp:

If Application.DisplayAlerts = False Then Application.DisplayAlerts = True
With Sheets("Cover").Range("E12")
  If .Value = "" Then
    MsgBox "Fill in missing value in Cell E12 on Cover", vbExclamation, "Can´t save"
    Exit Sub
    
  End If
  ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" _
    & .Value
   
End With
    Exit Sub
errtp:
    If Err.Number = 1004 Then
    MsgBox "file was not saved"
    Application.DisplayAlerts = False
           
    End If
       
End Sub

The problem is, if the file already exists it will ask for the replace/no/cancel prompt. Replace and cancel work fine, but when I press No it just keep repeating the prompt – all I want to do is exit the application without saving if No was selected.

Also in the newly generated file, the above code is also copied – I don’t want this, only want the data in the file and no other functionality. I hope this makes sense.