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 = True4. 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?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
