Wauvel

Excel like a finance pro.

← All VBA snippets

Run a macro at a set time

Refresh at 7am before anyone is in — and why that's often a job for a real scheduler.

1What it does

`Application.OnTime` asks Excel to run a macro at a future time: a refresh at 7:00, a save every thirty minutes, a reminder at 4:45. It genuinely works, and it has a limit that decides whether it's the right tool: Excel has to be open, on a machine that's awake, with that workbook loaded, when the time arrives. Close the file or put the laptop to sleep and the schedule is gone. For a recurring job that must happen whether or not anyone is at their desk, Windows Task Scheduler or Power Automate is the honest answer.

2The code

' Keep the scheduled time in a module-level variable so it can be cancelled.
Private nextRun As Date

Sub StartAutoRefresh()
    nextRun = Now + TimeSerial(0, 30, 0)          ' 30 minutes from now
    Application.OnTime EarliestTime:=nextRun, Procedure:="AutoRefresh"
End Sub

Sub AutoRefresh()
    ThisWorkbook.RefreshAll
    ThisWorkbook.Worksheets("Summary").Range("B2").Value = _
        "Last refreshed " & Format(Now, "hh:mm")
    StartAutoRefresh                              ' schedule the next one
End Sub

Sub StopAutoRefresh()
    ' Cancel needs the EXACT time it was scheduled for.
    On Error Resume Next
    Application.OnTime EarliestTime:=nextRun, Procedure:="AutoRefresh", _
                       Schedule:=False
    On Error GoTo 0
End Sub

' In ThisWorkbook: cancel on close, or Excel reopens the file to run it.
' Private Sub Workbook_BeforeClose(Cancel As Boolean)
'     StopAutoRefresh
' End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes▾
  1. 1

    Don't see the Developer tab in the ribbon?

    You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
    • Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
    • Mac: Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
  2. 2

    Paste in the code

    Press Alt + F11 to open the Visual Basic editor, then Insert → Module and paste the snippet's code into the blank window. Close it with Alt + Q.
  3. 3

    Run it

    Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
  4. 4

    Keep the macro — save as .xlsm

    File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
  5. 5

    Macros blocked?

    Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

  • Refresh a dashboard every thirty minutes while it's open on a screen.
  • Autosave a working file at intervals.
  • Pop a reminder at a set time during a working session.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

Application.OnTime queue
AutoRefresh—

Schedule a refresh, then see the two ways it goes wrong.

5Pitfalls

The schedule silently never runs because the laptop slept or the file was closed.

Fix: OnTime only lives while Excel does. For unattended jobs, use Windows Task Scheduler or Power Automate.

After closing the workbook, Excel reopens it on its own to run the pending macro.

Fix: Cancel the pending call in Workbook_BeforeClose, as above.

The cancel fails with error 1004 because it was given a different time.

Fix: Store the scheduled time in a module-level variable and cancel with exactly that value.

A repeating schedule is started twice and now runs every fifteen minutes.

Fix: Call StopAutoRefresh at the start of StartAutoRefresh before scheduling a new one.

6No-code alternatives

  • Windows Task Scheduler — Runs whether or not anyone is logged in, and survives reboots. The right tool for a job that must happen.
  • Power Automate — Cloud-scheduled refreshes of files in OneDrive or SharePoint, with no machine left on.
  • Query refresh on a timer — Data → Connection Properties → Refresh every N minutes does the refresh part with no code.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.