VBA Save As not Saving

[Data Monkey]

I've got some code that deletes a couple of tabs and then finished with a Save As box. Display Alerts is turned off for the second deleted tab (but I want it on for the first) but then turned back on before the Save As.

The Save As dialog box comes up and when I navigate to the desired file path (I don't want it specifying) I can see files in there but if I try to save over one of them it doesn't ask me if I want to like normal and it doesn't save.

Below is my code, please can someone let me know what I'm missing.

Sub Delete_Customer_Input()


    Application.ScreenUpdating = False
    Sheets("Customer Input").Delete
    Application.DisplayAlerts = False
    Sheets("Backup Data").Visible = True
    Sheets("Backup Data").Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Sheets("Position Selection").Select
    Range("A1").Select
Dim varResult As Variant
'displays the save file dialog
varResult = Application.GetSaveAsFilename( _
    FileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
End Sub

 

[wideboydixon]

As far as I'm aware, the GetSaveAsFilename() function will not prompt you if the destination file already exists. Note that this function will only retrieve a filename and not actually save the file. If you want to save the file (and get the overwrite prompt) then:

Sub Delete_Customer_Input()
    Application.ScreenUpdating = False
    Sheets("Customer Input").Delete
    Application.DisplayAlerts = False
    Sheets("Backup Data").Visible = True
    Sheets("Backup Data").Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Sheets("Position Selection").Select
    Range("A1").Select
    Dim varResult As Variant
    Dim exitSub As Boolean
    Do
        exitSub = True
        ' Displays the save file dialog
        varResult = Application.GetSaveAsFilename(FileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
        If varResult = False Then
            ' Decide whether you want to re-prompt here
            exitSub = False ' This will re-prompt for the filename
        Else
            If exitSub Then
                On Error Resume Next
                Err.Clear
                ActiveWorkbook.SaveAs Filename:=varResult, FileFormat:=xlOpenXMLWorkbookMacroEnabled
                exitSub = Err.Number = 0
            End If
        End If
    Loop Until exitSub
End Sub

WBD

 

[Data Monkey]

Perfect! Thank you so much!