Using VBA macro to SaveAs but keep original file open

Question

I am using a macro that runs automatically when opening my file, which includes a "SaveAs" function. However, I would like the original file to stay open after saving in case I need to double check the data or want to make a change. I have read that people suggest the use of the "SaveAsCopy" function, however, I need to save it as an .XLXS and not an .XLSM file, which "SaveAsCopy" doesn't seem to have the ability to do.

Any help would be appreciated!

Could you share the code? – VBasic2008 Aug 26 '22 at 12:32 — VBasic2008, Aug 26 '22 at 12:32

steveP · Answer 1 · 2022-08-26T13:08:42.300

0

Does this work:

Sub SaveWorkbook()

Dim wb, wbnew As Workbook
Dim FileName As String
Dim FilePath As String

Application.DisplayAlerts = False

Set wb = ThisWorkbook

FilePath = "C:\" 'Your File Path
FileName = "Saved File" 'Your File Name

wb.Activate

'Copies sheets to new workbook
ActiveWorkbook.Sheets.Copy
 
Application.Dialogs(xlDialogSaveAs).Show FilePath & FileName

'OR alternatively
'ActiveWorkbook.SaveAs FileName:=FilePath & FileName & ".xlsx"

ActiveWorkbook.Close

Application.DisplayAlerts = True

End Sub

edited Aug 26 '22 at 13:08

answered Aug 26 '22 at 12:33

steveP

79
7

You'll have to try harder. After running your code, the `.xlsx` file stays open instead of the initial `.xlsm` file. – VBasic2008 Aug 26 '22 at 12:36
Sorry my fault! – steveP Aug 26 '22 at 12:42

Using VBA macro to SaveAs but keep original file open

1 Answers1