Try the below..

Dim fSaveName As Variant
Do
fSaveName = Application.GetSaveAsFilename
Loop Until fSaveName < False
If Dir(fSaveName, vbNormal) < "" Then
If MsgBox("File already exists, Overwrite ?", vbYesNo + vbDefaultButton2) _
< vbYes Then Exit Sub
End If
Application.DisplayAlerts = True
ThisWorkbook.SaveAs fSaveName
Application.DisplayAlerts = False

If this post helps click Yes
---------------
Jacob Skaria


"Horatio J. Bilge, Jr." wrote:

I have noticed a problem with the SaveAs method I am using. Here is the code:
Dim fSaveName as Variant
Do
fSaveName = Application.GetSaveAsFilename
Loop Until fSaveName < False
ThisWorkbook.SaveAs fSaveName

If the file name/location I choose in the GetSaveAsFilename dialog already
exists, I am asked if I want to replace it.
Clicking Yes replaces the file as expected, but clicking No or Cancel leads
to an error: "Method 'SaveAs' of object '_Workbook' failed"

Is there a way to handle this situation?