Auto save Excel file without prompt and create a backup copy

[GEMINI528]

Dear Excel Guru’s,

I need help to create a VBA code to save an Excel file without prompt upon exit and create another file as backup (Save As) with filename plus the current date.

The file is save at G:\Data\Trans_Data.xlsb and the Save As file is to be save at Z:\Backup\Trans_Data MM-DD-YY.xlsb. If the file is Save at same date, overwrite the file without any prompt.

Thank you in advance.

[pjwhitfield]

Please Login or Register  to view this content.

however could I recommend saving the date as format YY-MM-DD, this will ensure all files are sensibly ordered.

[GEMINI528]

Thank you for your quick response.

It’s working fine with few revision.

When I open the file and re save it for the second time, it has a prompt “A file name….xlsb already exist in this location, do you want to replace it?”

Is there a possibility of saving it without a prompt?

Thank you.

[GEMINI528]

Also, when I click “NO”, there is a Run time error 1004, method Save As object failed. Then there is END and DEBUG button.

[Jim885]

Try this small adjustment to the code pjwhitfield gave you;

Please Login or Register  to view this content.