Excel like a finance pro.

The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.

← All VBA snippets

Copy one sheet out into its own file

You almost never send the model. You send one tab, with the links cut.

1What it does

Copying a worksheet with no destination creates a brand-new workbook containing just that sheet — one line. What follows is the part that matters: every formula that referenced another tab in the original file is now an external link pointing back at it, so the copy looks perfect on your machine and asks the recipient about a workbook they've never seen. Paste the values over the formulas before saving and the file is genuinely standalone, which is what you meant by "send them the schedule".

2The code

Sub ExportActiveSheet()
    Dim srcName As String, target As String
    Dim newBook As Workbook

    srcName = ActiveSheet.Name
    Application.ScreenUpdating = False

    ' Copy with NO argument = into a new workbook of its own.
    ActiveSheet.Copy
    Set newBook = ActiveWorkbook

    ' Formulas that pointed at other tabs are now external links home.
    ' Freeze them so the file stands on its own.
    With newBook.Worksheets(1).UsedRange
        .Value = .Value
    End With

    target = ThisWorkbook.Path & Application.PathSeparator & _
             srcName & " " & Format(Date, "yyyy-mm-dd") & ".xlsx"

    Application.DisplayAlerts = False       ' overwrite without prompting
    newBook.SaveAs FileName:=target, FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True

    newBook.Close SaveChanges:=False
    Application.ScreenUpdating = True

    MsgBox "Saved:" & vbNewLine & target, vbInformation
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
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
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
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
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
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

Send one schedule to an auditor without the whole model.
Split a monthly pack into per-department files.
Hand over a summary tab that won't prompt anyone about links.

4See it in action

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

In the model

Revenue	=PL!B7
Gross margin	=PL!B9

A normal cross-tab reference. Watch what happens to it when the sheet leaves the workbook.

5Pitfalls

The recipient gets a "this workbook contains links" prompt and figures they've been sent something broken.

Fix: `.Value = .Value` over the UsedRange before saving, as above — that's the whole reason this macro is longer than one line.

`.Value = .Value` also flattens the destination formatting expectations — conditional formats that referenced other sheets stop working.

Fix: Check for rules pointing off-sheet; they need the same treatment or removing.

SaveAs prompts about overwriting and the macro stops waiting for a click.

Fix: DisplayAlerts = False around the save, and ALWAYS back to True after.

Saving a sheet with macros on it as .xlsx silently drops them.

Fix: Use xlOpenXMLWorkbookMacroEnabled and a .xlsm name if the sheet has code behind it.

6No-code alternatives

Move or Copy by hand — Right-click the tab → Move or Copy → (new book). Same thing, without the link cleanup.
Export as PDF — Better when they only need to READ it — nothing to break, nothing to link.

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.