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.

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
   Sheets(vntToday).Select
   If Err <> 0 Then
   MsgBox "Worksheet doesn't exist."
   Else
   Range("A1").Select
   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.