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?

 

[Michael M]

Won't make any difference if the fpath or fname are wrong
But try

Sub BackupFile()
Dim sFileName As String, fname As String, dt As String, fpath As String
' the variable for the workbooks current directory
fpath = ThisWorkbook.Path
'the variable to create the filename
dt = Format(Now, "dd-mm-yy hhmm AM/PM")
' return the filename WITHOUT the file extension
fname = "Backup " & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".x") - 1)
'using the Backups Sheet
With Sheets("Backups")
' this line checks to see if the current date is 7 days or more
' newer than the date stored in "A1"
    If Date - Range("A1").Value >= 7 Then
'If it's not 7 days different then do nothing
' if the date IS newer then it inputs a new date in "A1"
    .Range("A1").Value = Date
' it then converts the date to a value rather than a date
' so it can't change each day
    .Range("A1").Value = .Range("A1").Value
' this puts the date into "A2" which will change each day
    .Range("A2").Value = Date
' this saves the workbook to the designated filepath
    ActiveWorkbook.SaveCopyAs Filename:=fpath & "\Backups\" & fname & " " & dt & ".xlsm"
    End If
End With
End Sub

 

[dpaton05]

This is so strange. I moved the file from my network drive where I was developing it to the network drive at my work, tried the same changes and it worked fine.

 

[Michael M]

See post #61, with changes

 

[dpaton05]

How is it any different?

 

[Michael M]

my response from post #60

fname = "Backup " & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".x") - 1)

 

[dpaton05]

Oh yeah, I see what you did now. Thanks Michael.

 

[dpaton05]

NNNOOOOOOOOOO!!!!!!!!!

Its stopped working again. Now it says Type mismatch and highlights this line

If Date - Range("A1").Value >= 7 Then

Sorry to be so annoying Michael.

 

[dpaton05]

Does A1/A2 need to be any specific format as they are formatted As date?

 

[Michael M]

both should be formatted as Date !

 

[dpaton05]

This is crazy Michael, I got in to work today, tried it again and it worked fine using this line of code

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

Thanks for your help.