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. For more information, see Show the Developer tab.

To use the example below, open a new workbook.

Important: VBA code cannot be undone, so make sure to test your code on a blank workbook, or a copy of an existing workbook. If the code doesn't do what you want, you can close the workbook without saving changes.

  1. Click Developer > Visual Basic.

    Code group on the Developer tab

  2. In the VBA Project Explorer on the left hand side, expand the VBA Project folder for your workbook, then double-click the ThisWorkbook module. If you don't see the Project Explorer, you can go to View > Project Explorer, or press Ctrl+R.

    ThisWorkbook module in the Visual Basic Editor (VBE)
  3. In the module window that opens on the right, insert the following code:

    Private Sub Workbook_Open()
    ' Put your code here
    End Sub
  4. Paste your recorded code in the Sub procedure between the Sub and End Sub lines.

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

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

The next time you open the workbook, the code you added to the Workbook_Open procedure will run automatically.

Before you get started, make sure the Developer tab is shown on the ribbon. To do that:

  1. On the menu, click Excel > Preferences... > Ribbon & Toolbar.

  2. In the Customize the Ribbon category, in the Main Tabs list, select the Developer check box.

  3. Click Save.

To use the example below, open a new workbook.

Important: VBA code cannot be undone, so make sure to test your code on a blank workbook, or a copy of an existing workbook. If the code doesn't do what you want, you can close the workbook without saving changes.

  1. Click Developer > Visual Basic.

  2. In the VBA Project Explorer on the left hand side, expand the VBA Project folder for your workbook, then double-click the ThisWorkbook module.

  3. In the module window that opens on the right, insert the following code:

    Private Sub Workbook_Open()
    ' Put your code here
    End Sub
  4. Paste your recorded code in the Sub procedure between the Sub and End Sub lines.

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

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

The next time you open the workbook, the code you added to the Workbook_Open procedure will run automatically.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Expand your skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×