How to use Save As function to automatically overwriting existing file in Excel?

When saving an Excel workbook to a new folder, you will get a prompt box as below screenshot shown if there is a same name workbook exists and locates on the folder. How to automatically overwriting the existing file without prompt warning message? Method in this article can help you.

Save as function to automatically overwriting existing file with VBA code

Save as function to automatically overwriting existing file with VBA code

If you want to save a workbook with a new name and automatically overwrite the existing file in Excel. Please do as follows.

1. Firstly please create a Command Button for triggering the Save as function in your worksheet. Please click Developer > Insert > Command Button (Active X Control). See screenshot:

2. Draw a Command Button on your worksheet. Then right click it and select View Code from the context menu.

3. In the Microsoft Visual Basic for Applications window, please copy the below VBA code and paste between the Private Sub and End Sub lines in the Code window.

VBA code: Save as function to automatically overwriting existing file

Dim xFileName As String
Application.DisplayAlerts = False
If Right(ActiveWorkbook.Name, 4) = "xlsm" Then
  xFileName = Application.GetSaveAsFilename(ActiveWorkbook.Name, "Excel Macro-Enabled Workbook (*.xlsm),*.xlsm")
Else
  xFileName = Application.GetSaveAsFilename(ActiveWorkbook.Name, "Excel Workbook (*.xlsx),*.xlsx")
End If
If (xFileName <> "") Or (xFileName <> "False") Then
  ActiveWorkbook.SaveAs Filename:=xFileName
End If
Application.DisplayAlerts = True

4. Press the Alt + Q keys to exit the Microsoft Visual Basic for Applications window. And turn off the Design Mode under the Developer tab.

5. Click the Command Button, then a Save As dialog box pops up, please select a folder to save this workbook, and then click the Save button.

If there is a same name workbook exists in the destination folder, it will be overwriting automatically with current workbook directly without prompt.

Related articles:

• How to save a worksheet as PDF file and email it as an attachment through Outlook?
• How to save an Excel filename with timestamp?
• How to save, export multiple/all sheets to separate csv or text files in Excel?
• How to disable or do not allow Save & Save As options in Excel?
• How to disable workbook save but only allow save as in Excel?