VBA to create backup file when closing excel

[sparky_13]

Hi,
I'm am extremely green using VBA. I was able to find info on creating a macro in VBA that would save a copy of my workbook to another location, as a backup; and I was able to modify where I wanted to save it. I linked this macro to a Button in my work book.

What I am looking for is, instead of clicking the button, can I add some code so that the macro runs when I hit the 'X' button to close excel?

Sub Backup_Files()
ActiveWorkbook.SaveCopyAs "D:\Time Sheets\BackUp_" + ActiveWorkbook.Name
End Sub

 

[AC PORTA VIA]

add below to ThisWorbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Fire when user closes Workbook
ActiveWorkbook.SaveCopyAs "D:\Time Sheets\BackUp_" + ActiveWorkbook.Name
End Sub

 

[sparky_13]

add below to ThisWorbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Fire when user closes Workbook
ActiveWorkbook.SaveCopyAs "D:\Time Sheets\BackUp_" + ActiveWorkbook.Name
End Sub

I've replaced my code with yours. When I close my test file, no "backup" file is made.

Like I said, I'm VERY new to VBA

 

[AC PORTA VIA]

Code needs to be in ThisWorkbook module to work on closing.

 

[sparky_13]

Ahhh, so not in a module. That seems to work with a test file.

I will test in my main file as well.

 

[sparky_13]

Works in my main file. Thank you AC PORTA VIA!

 

[AC PORTA VIA]

you are welcome but just so you know it will only save that file once.
there is nothing in the code that will overwrite already existing back up.

 

[sparky_13]

you are welcome but just so you know it will only save that file once.
there is nothing in the code that will overwrite already existing back up.

I added some text in a cell in my original file and saved.
closed the original file.
opened the "backup" file that the VBA saved as a copy, and confirmed the text was there.
closed the "backup" file.
opened original file, added more text in another cell and saved.
closed the original file.
opened the "backup" file that the VBA saved, 2nd cell text was there.

Still only have (1) "backup" file in the 'D:\Time Sheets' location.

So for me it seems to be working they way I was looking for.