Wauvel

Excel like a finance pro.

← All VBA snippets

Set the workbook up every time it opens

Land everyone on the summary, at the top, refreshed — and know it's the first thing IT blocks.

1What it does

`Workbook_Open` runs when the file opens, which means a model can greet every user the same way: on the summary sheet, scrolled to the top, filters cleared, queries refreshed, today's date stamped. It removes the "why is it showing the wrong tab?" conversation entirely. Two honest caveats belong on this page. It only runs if macros are enabled, so the file must still make sense when they aren't. And it's the first thing a cautious IT policy disables, so it can't be the only thing that makes the file correct.

2The code

' Goes in the ThisWorkbook code module.
Private Sub Workbook_Open()
    Dim ws As Worksheet
    On Error GoTo Cleanup
    Application.ScreenUpdating = False

    ' Clear any filters left on from last time, on every sheet.
    For Each ws In Me.Worksheets
        If ws.AutoFilterMode Then
            If ws.FilterMode Then ws.ShowAllData
        End If
    Next ws

    ' Refresh the queries so nobody reads last month's numbers.
    Me.RefreshAll

    ' Land on the summary, at the top.
    With Me.Worksheets("Summary")
        .Activate
        .Range("A1").Select
        .Range("B2").Value = "Opened " & Format(Now, "d mmm yyyy, hh:mm")
    End With
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 1

Cleanup:
    Application.ScreenUpdating = True
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

  • Open every copy of a model on the summary sheet.
  • Refresh the data connections so the file is never stale on open.
  • Clear leftover filters so nobody reads a filtered subset as the total.

4See it in action

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

What the user sees on open
Active sheet—
Filters—
Data—

5Pitfalls

Macros are disabled, so none of this runs — and the file opens on whatever tab it was saved on.

Fix: Save the file on the summary tab anyway. The macro should improve a file that already works without it.

RefreshAll runs in the background, so the next lines read data that hasn't arrived.

Fix: Turn off background refresh on each connection, or refresh them individually with BackgroundQuery:=False.

Placed in a normal module, it never runs.

Fix: Workbook_Open belongs in the ThisWorkbook module.

Writing a timestamp on open marks the file as changed, so everyone is asked to save on close.

Fix: Set Me.Saved = True at the end if the change shouldn't prompt.

6No-code alternatives

  • Save it on the right tab — No code at all, and works with macros disabled. Covers most of what this macro is used for.
  • Auto_Open in a module — The legacy equivalent. Workbook_Open is the modern one and more reliable.

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.