Code isn't working - AutoSave on all files

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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!

Register Yourself Login