CODEWITHSUNDEEP
vbaexcelbackup
Martin Carlsson
Martin Carlsson

Reputation: 471

VBA Excel Backup: Save copy of current workbook via VBA under a new name, keep VBA scripts, no prompt

I need to make a VBA script that can take a complete copy of the current workbook and save it under a new name.

It need to be a complete copy with VBA scripts and everything.

The user may NOT be prompted for anything - the new file name (and location) will be provided by the code.

Upvotes: 1

Views: 29580

Answers (3)

Mike - SMT
Mike - SMT

Reputation: 15226

I have noticed that at least for Excel 2013 ActiveWorkbook.SaveAs Filename:="C:\Data.xlsm may not work as on my end it is saying that excel cannot save as file type xlsm.

enter image description here

However I did find a work around that is working for myself and wanted to add it here as a reference for others who may run into the same problem.

If you add , FileFormat:=52 after your code it will be able to save as the xlsm format.

So this worked for me:

ActiveWorkbook.SaveAs Filename:="C:\Data.xlsm, FileFormat:=52"

Happy coding!

Upvotes: 1

Michal Jána
Michal Jána

Reputation: 31

Try this:

Activeworkbook.SaveCopyAs "C:\Data.xlsm"

Upvotes: 0

Roger Rowland
Roger Rowland

Reputation: 26279

Surely something like this is sufficient?

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Data.xlsm"
Application.DisplayAlerts = True

Upvotes: 10

Related Questions