Macro to create an automatic backup of file

[Legacy 260385]

Is it possible to create a macro that automatically saves a backup of the excel document in another desired location?

I have this formula:

Sub backupbutton()
Dim fname
fname = "D:\" & Format(Now, "dd mmm yy hh mm") & ".xlsm"
ThisWorkbook.SaveAs Filename:=fname
End Sub

But that just keeps on making multiple copies of the file rather than overwriting the backup in the D:\ location.
Also, that particular macro requires me to have to click on a button in order for it to work but I would prefer that it happens automatically when the original file is saved.

Thank You

 

[Artik]

Try this macro:

Sub backupbutton_1()
'http://www.mrexcel.com/forum/excel-questions/735411-macro-create-automatic-backup-file.html
  Dim fname As String
  Dim sDir As String
  Dim sFullName As String
  
  sDir = Dir("d:\Backup *.xlsm")
  
  If sDir <> "" Then
    Do
      If sDir Like "Backup ## ??? ## ## ##.xlsm" Then
        Kill "D:\" & sDir
      End If
      sDir = Dir
    Loop Until sDir = ""
  End If
  
  fname = "D:\Backup " & Format(Now, "dd mmm yy hh mm") & ".xlsm"
  sFullName = ThisWorkbook.FullName
  
  ThisWorkbook.Save
  ThisWorkbook.SaveAs Filename:=fname
  
  Workbooks.Open Filename:=sFullName
  ThisWorkbook.Close False
End Sub

Artik

 

[Legacy 260385]

Already got it working but thanks!!