Code isn't working - AutoSave on all files

  • So I've been trying to put together some code that AutoSaves any and all open documents into a single folder every 5 minutes (code is set to 10 seconds for testing purposes). Saves as original filename + date and as extension xlsx. I can get it to run manually but not automatically. Below is the code I have in a module in my Personal.xlsb. Ideas on what I'm doing wrong? I'm not particularly good at code so you may have to speak slowly. What I do have is somewhat cobbled together from a lot of other similar examples out there, but I didn't find any that fit exactly.

    [VBA]Private Sub Workbook_Open()
    dTime = Now + TimeValue("00:0:10")
    Application.OnTime dTime, "AutoSaveMacro"
    End Sub

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

    Public dTime As Date
    Sub AutoSaveMacro()
    dTime = Now + TimeValue("00:00:10")
    ThisWorkbook.SaveCopyAs Filename:= _
    "C:\AutoSave\" & _
    Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".", , vbTextCompare) - 1) & _
    "_" & Format(Date, "yyyy-mm-dd") & ".xlsx"
    End Sub[/VBA]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!