Automatically run a macro when opening a workbook

You might want a macro you recorded to run automatically when you open a specific workbook. The following procedure uses an example to show you how that works. You may also want to run macros automatically when Excel starts.

Before you get started, make sure the Developer tab is shown on the ribbon. If it’s not there, do the following:

  1. Click File > Options > Customize Ribbon.

  2. Under Customize the Ribbon, in the Main Tabs box, check the Developer box.

  3. Click OK.

To use the example below in your own workbook, create sheets for each day of the month and name each sheet tab accordingly. For example, name them March 1, March 2, and so on, through March 31. The macro will automatically run when you open the workbook showing the tab for the current day of the month.

  1. Click Developer > Visual Basic.

Code group on the Developer tab

  1. Click Insert > Module.

  2. In the module, insert the following code:

    Private Sub Auto_Open()
    Dim vntToday As Variant
    vntToday = WorksheetFunction.Text(Date, "mmmm dd")
    On Error Resume Next
    If Err <> 0 Then
    MsgBox "Worksheet doesn't exist."
    End If
    End Sub

  3. Close Visual Basic (you don’t have to save anything).

  4. Save the workbook as an Excel Macro-Enabled Workbook (*xlsm) and close it.

The next time you open the workbook, the tab for the current day will be selected, with the cursor in cell A1. If the worksheet for the day doesn’t exist, the “Worksheet doesn’t exist” message appears.

Tip:  You could create a new workbook for each month as it comes up, and create the macro again for the new workbook.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!