How to save copy of Excel workbook?

Question

I've been trying to make a macro that automatically saves a backup copy of my Excel workbook before I edit any of its data.

Every time I call it, even though it successfully saves a new copy, when it reaches the end the code stops execution, and macros I try to call below it don't execute.

I think when I run the code and the new backup version is created, the code continues to run on the backup workbook instead of the main workbook, so when I close the backup workbook I abruptly end the code.

I'd like to save a backup version of my main workbook, close the backup version and continue the code on the main workbook.

Public Sub BackupWorkbook()

Dim CurrentFile As String, BackupFile As String, DesiredWorkbookName As String
Dim NowDate As String

'Save current code and Excel spreadsheet data
ActiveWorkbook.Save

'Get necessary strings for filenames
CurrentFile = ThisWorkbook.FullName

NowDate = Replace(Format(Now, "dd-mm-yyyy, hh:mm:ss"), ":", ".")
BackupFile = ThisWorkbook.Path & "\" & "Chem Chart Backups" & "\" & "Chemical Chart" _
                & " (" & NowDate & ")" & ".xlsm"

'Save as active workbook to backup file location, then reopen main workbook
ActiveWorkbook.SaveAs BackupFile, FileFormat:=52

Workbooks.Open CurrentFile

'This should close the backup version of workbook that opened because of SaveAs method
Workbooks(DesiredWorkbookName & " (" & NowDate & ")" & ".xlsm").Close SaveChanges:=True

End Sub

Public Sub TestMacros()

Call BackupWorkbook

'If this message box pops up after macro is called, it is successful
MsgBox "Success!" 

End Sub

Answer 1

I use

Sub BackUp()

    Dim BackUpPath As String
        BackUpPath = "Your path"
        
    Dim BackUpFile As String
        BackUpFile = BackUpPath & "BackUp.xlsm"
        
        ThisWorkbook.SaveCopyAs Filename:=BackUpFile
        
End Sub

I call this at various times in my project to backup the document as required, this seems to work okay for me and my code will continue to run in the original workbook.

I also generate a unique filename each time and do not actually use "BackUp" as the name of the new workbook as this overwrites my previous backup so I would recommend doing something similar.