Excel VBA Workbook Events6 Automation Macros with Copy-Paste Code

What Are Workbook Events

An event is an action Excel recognises and can respond to automatically. The workbook opens: that is an event. A cell on Sheet2 changes: that is an event. The workbook is about to close: that is an event. You attach VBA code to the event and the code runs the moment that action occurs. No button. No manual trigger. No one needs to remember.

This matters most in shared finance workbooks where the weak link is always the human step. Protection gets left off. Saves get skipped. The same file gets passed around and nobody can tell which version is current. Event-driven VBA removes the human from that chain.

Excel has dozens of events. The six in this guide are the ones with genuine daily use in finance workflows: Workbook_Open, Workbook_BeforeClose (two separate use cases), Worksheet_Change, Workbooks.Add, and Application.GetOpenFilename.

The Module Question: Where Code Must Go

This is the one thing that breaks most event-procedure setups. Workbook-level events (Workbook_Open, Workbook_BeforeClose) must go in the ThisWorkbook module. Worksheet-level events (Worksheet_Change) must go in the individual sheet module for the sheet you want to monitor. Standard modules (Module1, Module2) are for manually triggered subs and functions only. Paste an event procedure into Module1 and it will never fire.

Workbook_Open: Auto-Unprotect on Open

Problem it solves: A protected worksheet in a shared workbook. Users open it and immediately hit a protection error before they can do anything useful. They either break the protection themselves (and forget to re-apply it) or they call you. Workbook_Open unprotects the correct sheet automatically the moment the file opens.

Place this in the ThisWorkbook module:

Private Sub Workbook_Open()
    ' Unprotect the data entry sheet on open
    ' Combine with Workbook_BeforeClose to re-protect on close
    Dim wsData As Worksheet
    Set wsData = ThisWorkbook.Sheets("Data Entry")
    wsData.Unprotect Password:="YourPasswordHere"
End Sub

Replace "Data Entry" with the exact name of your target sheet (case-sensitive). Replace "YourPasswordHere" with your actual password, or remove the Password argument entirely if the sheet has no password set.

If you need to unprotect multiple sheets, add one Unprotect line per sheet before End Sub. The Workbook_Open event fires once per open, so all lines execute in sequence.

Workbook_BeforeClose: Force Save Before Close

Problem it solves: Someone presses the X, clicks "Don't Save" on the dialog, and your data is gone. This happens in shared workbooks constantly. The macro below intercepts the close action and forces a save before Excel closes the file. The user cannot bypass it.

Place this in the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Force save before allowing the workbook to close
    ' The user cannot skip this save
    If Me.Saved = False Then
        Me.Save
    End If
End Sub

Me.Saved = False checks whether the workbook has unsaved changes. If it does, Me.Save saves immediately. If there are no unsaved changes, the save is skipped. The Cancel As Boolean parameter in the procedure header gives you the option to cancel the close entirely: set Cancel = True inside the sub if you want to prevent closing under specific conditions.

Workbook_BeforeClose: Protect Sheet on Close

Problem it solves: The inverse of the open scenario. Users unprotect a sheet to do their work, then close the file without re-protecting it. The next person who opens it finds an unprotected sheet. This macro re-applies protection on every close automatically.

If you already have a force-save procedure in Workbook_BeforeClose, combine both actions in the same procedure. You cannot have two procedures with the same name in one module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' 1. Protect the data entry sheet
    Dim wsData As Worksheet
    Set wsData = ThisWorkbook.Sheets("Data Entry")
    wsData.Protect Password:="YourPasswordHere", _
        DrawingObjects:=True, Contents:=True, Scenarios:=True

    ' 2. Save after protecting (so the protected state is saved)
    If Me.Saved = False Then
        Me.Save
    End If
End Sub

The order matters. Protect first, then save. If you save before protecting, the file closes with protection off until someone opens it again and triggers Workbook_Open.

Worksheet_Change: Auto-Save on Cell Edit

Problem it solves: Data entry into a live tracking sheet where every entry needs to be persisted immediately. Month-end accruals, journal entry logs, budget actuals: any workbook where a lost entry means a lost record. Worksheet_Change fires the instant any cell on the sheet is edited. This version restricts the save trigger to a specific range so it does not fire on every change across the whole sheet.

Place this in the sheet module for the sheet you want to monitor (right-click the sheet tab, select View Code):

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Auto-save when any cell in the data entry range changes
    Dim TriggerRange As Range
    Set TriggerRange = Me.Range("B2:F100")

    ' Only save if the changed cell overlaps with the trigger range
    If Not Intersect(Target, TriggerRange) Is Nothing Then
        Application.EnableEvents = False     ' Prevent re-triggering
        On Error GoTo Cleanup
        ThisWorkbook.Save
    End If

Cleanup:
    Application.EnableEvents = True          ' Always re-enable events
End Sub

The Application.EnableEvents = False line is not optional. Without it, the act of saving can trigger another Change event, which triggers another save, which triggers another Change event. The loop runs until Excel crashes or you force-quit it. The On Error GoTo Cleanup block ensures events are re-enabled even if the save fails. If you skip the error handler and a runtime error occurs, events stay disabled until you manually reset them or restart Excel.

Adjust "B2:F100" to your actual data entry range. The Intersect method returns Nothing if the changed cell is outside this range, so the save only fires on relevant changes.

Workbooks.Add: Create a New Workbook via VBA

Problem it solves: Monthly reporting where each month's output needs its own file. Instead of manually creating a new workbook, copying a range, and saving under a new name, a single macro handles all three steps. The finance team runs the macro and the output file appears in the target folder.

This is not a workbook event — it is a method you call from a standard module or a button-triggered macro. It belongs here because it is frequently used alongside open and close events in automated reporting workflows:

Sub CreateMonthlyOutputWorkbook()
    Dim wbNew As Workbook
    Dim wbSource As Workbook
    Dim wsOutput As Worksheet
    Dim SavePath As String

    Set wbSource = ThisWorkbook
    SavePath = "C:\Finance\Reports\Monthly-" & Format(Now, "YYYY-MM") & ".xlsx"

    ' Create a new blank workbook
    Set wbNew = Workbooks.Add

    ' Copy data from source to new workbook
    wbSource.Sheets("Output").Range("A1:Z500").Copy _
        Destination:=wbNew.Sheets(1).Range("A1")

    ' Rename the first sheet in the new workbook
    wbNew.Sheets(1).Name = "Report"

    ' Save and close the new workbook
    Application.DisplayAlerts = False
    wbNew.SaveAs Filename:=SavePath, FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    wbNew.Close SaveChanges:=False

    MsgBox "Report saved: " & SavePath, vbInformation
End Sub

Workbooks.Add creates the new workbook and returns a reference to it. The reference (wbNew) is then used to name the sheet, paste data, and save. Application.DisplayAlerts = False suppresses the overwrite confirmation dialog if the file already exists. Always set it back to True immediately after.

Adjust the SavePath and source range to match your actual folder structure and output range. This macro can also be called from a Workbook_BeforeClose event if you want the output file created automatically every time the source workbook is closed.

GetOpenFilename: Let the User Pick a File

Problem it solves: Macros that hard-code a file path break the first time someone moves or renames the source file. Application.GetOpenFilename presents the system file picker so the user selects the file at runtime. The macro gets a valid path every time without ever hard-coding it.

Sub OpenUserSelectedWorkbook()
    Dim FilePath As Variant
    Dim wbTarget As Workbook

    ' Show file picker filtered to Excel files only
    FilePath = Application.GetOpenFilename( _
        FileFilter:="Excel Files (*.xls;*.xlsx;*.xlsm;*.xlsb), *.xls;*.xlsx;*.xlsm;*.xlsb", _
        Title:="Select the source workbook", _
        MultiSelect:=False)

    ' Exit if user pressed Cancel
    If FilePath = False Then
        MsgBox "No file selected. Operation cancelled.", vbInformation
        Exit Sub
    End If

    ' Open the selected workbook
    Set wbTarget = Workbooks.Open(Filename:=FilePath)

    ' Your processing code here
    ' Example: copy data from the opened workbook
    wbTarget.Sheets(1).Range("A1:D100").Copy _
        Destination:=ThisWorkbook.Sheets("Import").Range("A1")

    ' Close the source workbook without saving
    wbTarget.Close SaveChanges:=False
End Sub

GetOpenFilename returns the full file path as a string if the user picks a file, or False (Boolean) if they click Cancel. The FilePath = False check handles the cancel case. Note the return type is declared as Variant rather than String specifically because it can return either a string or a Boolean.

The FileFilter argument controls which file types appear in the picker. The format is "Display name, *.ext". To show all files instead, pass "All Files (*.*), *.*".

Combining Events in One Workbook

A realistic finance workbook needs more than one event. A shared journal entry log, for example, needs: unprotect on open (so users can enter data), auto-save on each entry (so nothing is lost), and protect and save on close (so the sheet is locked before it lands in the next person's inbox). That is three event procedures in two different modules.

The ThisWorkbook module ends up with Workbook_Open and Workbook_BeforeClose. The journal sheet module gets Worksheet_Change. Each procedure is independent. They do not conflict. They each fire on their own trigger and leave the others alone.

If you are building a new shared workbook from scratch, start with Workbook_Open and Workbook_BeforeClose in ThisWorkbook. Add Worksheet_Change to the sheet module once the sheet structure is finalised. The Workbooks.Add and GetOpenFilename subs go in a standard module and are called from buttons or from other procedures.

Common Errors and Fixes

Frequently Asked Questions