Create Backup Copy using Excel VBA

[game_federer]

Hi All,

I wanted to create a backup copy before I run the subroutines Ive written for my file.
Ive used the following code.

path_new = ActiveWorkbook.Path
    ActiveWorkbook.SaveAs Filename:=path_new & "\Backup_Copy"

The problem is it closes the original file and runs the macros created on the backup copy.
Your help would be greatly appreciated.

 

[YasserKhalil]

Hello
Try this code

Sub Auto_Save()
    Dim saveDate As Date
    Dim saveTime As Variant
    Dim formatTime As String
    Dim formatDate As String
    Dim backupFolder As String

    saveDate = Date
    saveTime = Time

    formatTime = Format(saveTime, "hh.MM.ss")
    formatDate = Format(saveDate, "DD - MM - YYYY")

    Application.DisplayAlerts = False
        backupFolder = ThisWorkbook.Path & "\"
        ActiveWorkbook.SaveCopyAs Filename:=backupFolder & Replace(ActiveWorkbook.Name, ".xlsm", "") & " " & formatDate & " " & formatTime & ".xlsm"
    Application.DisplayAlerts = True

    MsgBox "Backup Successfully In The Path " & backupFolder
End Sub

 

[Monty]

YasserKhalil...

Really Masterpiece of code!

 

[YasserKhalil]

Thanks a lot Mr. Monty
I am just a learner. I have learned a lot from you

 

[Monty]

Thanks to you Yasser!

 

[game_federer]

Hello
Try this code

Sub Auto_Save()
    Dim saveDate As Date
    Dim saveTime As Variant
    Dim formatTime As String
    Dim formatDate As String
    Dim backupFolder As String

    saveDate = Date
    saveTime = Time

    formatTime = Format(saveTime, "hh.MM.ss")
    formatDate = Format(saveDate, "DD - MM - YYYY")

    Application.DisplayAlerts = False
        backupFolder = ThisWorkbook.Path & "\"
        ActiveWorkbook.SaveCopyAs Filename:=backupFolder & Replace(ActiveWorkbook.Name, ".xlsm", "") & " " & formatDate & " " & formatTime & ".xlsm"
    Application.DisplayAlerts = True

    MsgBox "Backup Successfully In The Path " & backupFolder
End Sub

Thanks YK for this code.
Helped me a ton and my client was very happy with it.

 

[YasserKhalil]

You're welcome. Glad I can offer some help