Wauvel

Excel like a finance pro.

← All VBA snippets

Pull figures out of another file and close it again

Read last month's numbers without leaving the file open or creating a link.

1What it does

A linked formula pointing at another workbook is a liability: it prompts on open, breaks when the file moves, and shows stale numbers when it can't be reached. Opening the file in code, reading what you need, and closing it leaves no link behind — the value is just a value. Two details make it behave: `ScreenUpdating = False` so the other file doesn't flash on screen, and `Close SaveChanges:=False` so you can never accidentally write to a file you only meant to read.

2The code

Sub PullPriorMonth()
    Dim src As Workbook
    Dim priorRevenue As Double
    Dim prevScreen As Boolean

    prevScreen = Application.ScreenUpdating
    On Error GoTo Cleanup
    Application.ScreenUpdating = False

    Set src = Workbooks.Open( _
        FileName:=ThisWorkbook.Path & Application.PathSeparator & "Prior month.xlsx", _
        ReadOnly:=True, _
        UpdateLinks:=False)

    ' Read straight off the object — no Activate, no Select.
    priorRevenue = src.Worksheets("PL").Range("B7").Value

    ' SaveChanges:=False means we can't write to a file we only read.
    src.Close SaveChanges:=False
    Set src = Nothing

    ThisWorkbook.Worksheets("Summary").Range("C7").Value = priorRevenue

Cleanup:
    Application.ScreenUpdating = prevScreen
    If Not src Is Nothing Then src.Close SaveChanges:=False
    If Err.Number <> 0 Then MsgBox "Couldn't read the prior month: " & Err.Description, vbExclamation
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

  • Bring last month's closing balances forward without a linked formula.
  • Read a rate or assumption from a central file each run.
  • Consolidate figures from several files into one summary.

4See it in action

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

  1. Idle — Summary!C7 is empty
  2. ScreenUpdating off · opening Prior month.xlsx read-only
  3. Read PL!B7 = 441,200
  4. Closed with SaveChanges:=False — no link left behind
Summary
Prior-year revenue (C7)—
This year486,000

A linked formula would do this too — and prompt on every open, then show a stale number the day the file moves.

5Pitfalls

The file is left open when the macro fails partway, and it's invisible because screen updating is off.

Fix: Close it under a Cleanup label, guarded by `If Not src Is Nothing` — as above.

`UpdateLinks` not specified, so opening the source pops ITS own update-links prompt and the macro stops waiting for a click.

Fix: Pass `UpdateLinks:=False` explicitly.

Workbooks.Open errors 1004 when the path is wrong, and the message doesn't say which file.

Fix: Build the path into a variable and include it in the error message.

Reading via Activate and Selection, which breaks the moment anything else has focus.

Fix: Read from the object directly — `src.Worksheets("PL").Range("B7").Value`.

6No-code alternatives

  • A linked formula — Simpler, and it updates on its own — at the cost of a prompt on every open and a break when the file moves.
  • Power Query — Better for anything table-shaped, and it refreshes without code. This is for grabbing a few specific cells.

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.