Macro - Save specific open wrksheet with a varying file name

[wrmcmahan12]

Hello Everyone,

Hopefully this is an easy fix. I have a large file that I backup every 30 minutes due to it corrupting frequently. I have created a macro to save a new version of the file with a variable name (based on date and time) as follow:
"YYYY-MM-DD-HH-MM_FileName.xlsm". It works out great when its the only file in use, but I have realized if I have it open with other files, which ever file that is active at that 30 minute mark is the one to get saved. Is there a way to tell the macro to activate the specific workbook I need (which name is variable) and save it? Currently I tried referencing a cell that has the file name in it, but still no luck. The only other idea I had was to save a backup, and then resave the file within the same job to a common name that is more or less static. Any thoughts? Current code below: (Private subs in workbook module, others in regular module)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "Save_Backup", , False
End Sub

Private Sub Workbook_Open()

dTime = Now + TimeValue("00:00:05")
Application.OnTime dTime, "Save_Backup"
End Sub

Public dTime As Date
Sub Save_Backup()

dTime = Now + TimeValue("00:30:00")
Application.OnTime dTime, "Save_Backup"
ActiveWorkbook.SaveAs Filename:="File path etc" & Format(Now, "yyyy-mm-dd-hh-mm") & "_File_Name.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

 

[Yongle]

Re: Need help with macro - Save specific open wrksheet with a varying file name

[COLOR=#000080][I]Instead of[/I][/COLOR]
ActiveWorkbook.SaveAs Filename:="File path etc...
[I][COLOR=#000080]Try[/COLOR][/I][COLOR=#ff0000]
[/COLOR]ThisWorkbook.SaveAs Filename:="File path etc...

The macro is in the file being saved, so you can refer with ThisWorkbook