Macro to auto create backup workbook to another folder upon saving the original

[Raceman]

Hi, I haven't been able to figure out a macro that , upon saving my file, will automatically create a backup file (my original file name plus "backup" added to the end), and save it to my desktop. Outside of this macro, my assumption is that each time the backup is saved there will be a message that says something like: "do want to replace the existing backup file on my desktop", that works ok for me. It's ok if it doesn't ask the question too.

Of course I should see no difference in the way my original file is saved. I will click the save button, my original file will save in the usual place and the backup will replace the previous backup file on my desktop.

 

[ParamRay]

.
.

Place this event-handler procedure in the code module ThisWorkbook within your workbook:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

    Dim sPath As String
    Dim vName As Variant
    
    sPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    vName = Split(Me.Name, ".")
    
    With Application
        .DisplayAlerts = False
        .EnableEvents = False
    End With
    
    Me.SaveCopyAs _
        Filename:= _
            sPath & Application.PathSeparator & _
            vName(0) & "_backup." & vName(1)
    
    With Application
        .DisplayAlerts = True
        .EnableEvents = True
    End With

End Sub

 

[Raceman]

I am probably doing something wrong, but this didn't work. Am I suppose to substitute my name into the code somewhere? What is "wScript.Shell"?

 

[ParamRay]

I am probably doing something wrong, but this didn't work. Am I suppose to substitute my name into the code somewhere? What is "wScript.Shell"?

You must place this procedure in the code module ThisWorkbook within your workbook.

Then, whenever you save your workbook, it will create the backup you wanted.

 

[Raceman]

I have placed it in This Workbook section on a test file and when I save my file nothing saves to my desktop as a Backup file. I did the test when I first received your post and again today and nothing happens (that I am aware of anyway)

 

[Smitty]

Jan Karel Piertese has a free AutoSave add-in that should do what you want.

HTH,

 

[Raceman]

Ok thank you, I'll check it out.