VBA macro that automatically saves copy of Excel workbook ends abruptly-CodePudding

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.

But every time I call it, even though it successfully saves a new copy, when it reaches the end of the macro the code completely stops execution, and any macros I try to call below it don't get executed.

I think what's happening is 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, but I'm stumped. Anyone know what I'm doing wrong/how to fix this?

TIA!

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

CodePudding user response：

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.