Creating a backup that runs once a week

[dpaton05]

I have a spreadsheet that I want to create a backup copy of the file once a week upon opening, for instance, when the spreadsheet is opened, the date of the last backup is checked and if the date it was created is more than 1 week ago, it will create a backup file.

This spreadsheet has a folder called Backups in the same folder it is in, which is where the backups are to be stored. The filename of the backup files are to be structured like this. Firstly, it needs the word Backup, then the name of the file that is being backed up, without the extension then it needs the date and time that it was backed up. The format for the last bit needs to be dd.mm.yy MMHH AM/PM, for instance "Backup Filename 24.3.21 129PM"

I found a very similar post that I posted a bit under a year ago but it was asking for a daily backup. I want a weekly backup now and I was not quite sure how to modify it. A lot of the other details are the same but any details in this post, takes precedence over details in that post I found.

Backups

I have a spreadsheet that is going to record data that I don't want to lose. Therefore, I want to insert a feature that runs a daily backup and saves a copy of the entire workbook as a separate file. I need the file saved with the filename "Allocation sheets - dd.mm.yyyy". The dd.mm.yyyy...

www.mrexcel.com

I also want the ability to easily change the frequency of Backups. Possibly by having a cell that I can enter in weekly, fortnightly or monthly to set the frequency. This can be done on a sheet with the code name data, somewhere between A37:A50.

Could someone help me please?

 

[dpaton05]

That makes sense now, thank you Michael!

 

[dpaton05]

I am trying to get the word backup before the original filename and I am getting the file not found error.

What is wrong with my line of code?

ActiveWorkbook.SaveCopyAs FileName:=fpath & "\Backups\" & "Backup" & " " & fname & " " & dt & ".xlsm"

 

[Michael M]

Change this

fpath & "\Backups\" & "Backup" & " " & fname & " " & dt & ".xlsm"

to

fpath & "\Backups\Backup" & " " & fname & " " & dt & ".xlsm"

 

[dpaton05]

I did that and I get this error

with the end if in the code highlighted.

 

[dpaton05]

I restarted my computer, added the changes again and now it just highlighting this line saying it can't find the file

    ActiveWorkbook.SaveCopyAs FileName:=fpath & "\Backups\Backup" & fname & " " & dt & ".xlsm"

 

[Michael M]

have you checkd to make sure fpath and fname are creating the correct variables ??
Ahh...needs a space after Backup

 ActiveWorkbook.SaveCopyAs FileName:=fpath & "\Backups\Backup " & fname & " " & dt & ".xlsm"

 

[dpaton05]

The sub works perfectly until I try to add the word Backup to the end of the directory, so

This works

ActiveWorkbook.SaveCopyAs FileName:=fpath & "\Backups\" & fname & " " & dt & ".xlsm"

Whether I have a space after backups or not, still gives me the same error.


This

ActiveWorkbook.SaveCopyAs FileName:=fpath & "\Backups\Backups" & fname & " " & dt & ".xlsm"

or

ActiveWorkbook.SaveCopyAs FileName:=fpath & "\Backups\Backups " & fname & " " & dt & ".xlsm"

doesn't work. Gives me the can't find file error.

 

[Michael M]

The code works fine for me on my directories.
Shouldn't the 2nd Backups be without the "s", just "Backup "

 

[dpaton05]

I changed that and nothing happened.

 

[dpaton05]

Is there another way of including the word Backup at the beginning of the filename?