Today, I’ll show you how to run a macro when opening Microsoft Excel.

Imagine you’re preparing for a weekly sales report, and every time you open your Excel file, you want it to clean the data, apply filters, and refresh pivot tables, all without lifting a finger.

That’s exactly what running a macro at startup can do for you. It’s like assigning a reliable assistant who jumps into action the moment you walk into the office. But I know it sounds tricky.

Setting up Excel VBA to automatically run a macro when a file opens isn’t exactly beginner stuff. Still, this tutorial breaks it down for you step by step.

Keep reading, and you’ll have it working in no time. I’ll ensure that the steps are simple enough so you can follow them even if you don’t come from a coding background. Let’s get started!

Using Workbook_Open Event in VBA

One of the most reliable ways to run a macro automatically when you open an Excel workbook is by using the Workbook_Open event in Excel VBA.

This method involves writing the macro inside a special event. When you open the workbook, the event is triggered, and the macro runs.

It lives inside the ThisWorkbook object in the VBA editor. It’s kind of like setting instructions in the control room of your Excel file. The best part? You don’t need to click anything once it’s set up. Excel executes the task as soon as the workbook is opened.

Whether you’re preparing a dashboard, refreshing data, or formatting sheets for daily tasks, this approach ensures your macro is ready to fire as soon as the file is opened. Now, let’s see below how to use this method.

What Do You Need?

Before you begin, you need an Excel workbook where you wish to insert a VBA script as a macro. The script can be in a document file or already inserted into the workbook.

To use Excel VBA, you must activate the Developer tab. Find below a simple Excel tutorial to learn how to do this.

Now that you can access the Code commands block in the Developer tab of Excel, you must learn how to use a VBA script to set up a macro. Find below a quick guide that you can follow to learn the process:

If you’re ready to use Excel VBA, start with the steps outlined below:

Open your Excel file and press Alt + F11 to launch the Visual Basic for Applications editor. This shortcut opens up the backend where all your macro magic lives.

In the Project Explorer window (usually on the left), double-click on ThisWorkbook under your file’s name. This opens a code window specifically tied to that workbook, not just a single sheet or general module.

From the two drop-downs at the top of the code window, select Workbook from the left and then Open from the right.

This creates a new subroutine called Workbook_Open(). It tells Excel, “Hey, run what’s inside here when this file opens.”

Write your macro code inside this subroutine. For example, copy and paste the following VBA script:

Sub TypeHelloWorld()
    Range("A1").Value = "Hello World"
End Sub

You can insert any action your macro should perform here. It could be anything from importing data to running calculations. You only need to enter the code or script between Sub TypeHelloWorld() and End Sub in the source script file.

Now, click on the Save button in the Visual Basic for Applications editor interface.

A Microsoft Excel dialog box will open. Click on the Go Back button.

The Save As dialog box will show up.

Click on the Save as type drop-down list and select the Excel Macro-enabled Workbook option.

Click Save to change the workbook’s file format to a macro-compatible Excel workbook. This step is crucial for the automated script to work.

That’s it! Close and reopen the Excel workbook. The macro will run automatically. You might see the Enable Content notification at the top of the Excel formula bar. You must click on that to allow the macro to run automatically.

Using Auto_Open Macro

If you don’t wish to attach a macro to the workbook and only want to run it exclusively for a specific worksheet, you use this method. Here, you don’t need to run the macro from the ThisWorkbook property. Instead, you run the macro as it’s in the regular module. However, you use the Sub Auto_Open() event instead of a general Sub headers.

What makes Auto_Open especially handy is that it’s easy to implement. It doesn’t require connecting the macro to any workbook event handlers, which can feel a bit technical at first. It’s also useful when you’re working with older files or templates that need to run the same task every time, like refreshing a dataset or applying formatting.

Follow along with the steps mentioned below to learn and practice this method:

Open the Excel workbook and navigate to a worksheet where you wish to run a macro when the Excel workbook is launched.

Press Alt + F11 to launch the Excel VBA Editor interface.

In the VBA editor, go to the top menu and click Insert, then choose Module. This creates a standard code module where your Auto_Open macro will be placed.

In the new module window, type the following code: Sub Auto_Open() and hit Enter to create the subroutine.

On the next line, add the actions you want Excel to perform. For example, use the following code:

Sub Auto_Open()
    Range("A1").Value = "Hello World"
End Sub

Copy and paste the above-mentioned VBA script in the blank Module and click on the Save button.

You must save the workbook as an Excel Macro-enabled Workbook. Follow the steps mentioned in the first method.

Now, close the workbook. Open the workbook again, and the macro will execute automatically.

You might see the Enable Content button at the top of the worksheet. Click on that to execute the macro automatically.

The drawback of this method is that it only works in a specific worksheet where you create the Excel VBA macro Module.

So, if you wish to execute macros as you open the workbook and also in multiple worksheets, but not all of them, add the macro individually to all the worksheets.

Attaching Macro to Personal Macro Workbook

Sometimes, you might need to run a macro when opening any workbook in your system. For example, attaching a time stamp, date, or company branding assets, etc. In this case, you need to use this method. Here, you insert the macro script inside the Personal Macro Workbook.

This method is especially helpful if you use the same automation task across different workbooks or projects. For instance, it’s perfect for cleaning data, setting default formats, or opening certain files every morning. You don’t need to copy the macro into every new workbook, which saves time and reduces mistakes.

Here are the steps you must follow to learn and practice this quick and effective method:

Open the Excel workbook and access any worksheet in the workbook where you wish to execute a VBA script automatically. It doesn’t matter which worksheet because the script will work in all the workbooks in your system.

If you haven’t created the Personal Macro Workbook yet, go to the Developer tab and click on the Record Macro command in the Code commands block.

The Record Macro dialog box will open. Type in the relevant data in the following fields:

• Macro name: Anything you want, it’s a dummy macro.
• Shortcut key: Ctrl + any alphabet from the keyboard, like q.
• Store macro in: Click on the drop-down list and select the Personal Macro Workbook option.
• Description: Type anything here.

Excel will create the Personal Macro Workbook on your PC.

Press Alt + F11 to open the Excel VBA Editor interface and look for the PERSONAL.XLSB project on the left side navigation panel.

Now, double-click on the PERSONAL.XLSB property, right-click, and hover over the Insert menu on the context menu.

Click on the Module option in the overflow menu on the right side.

A blank Module will open inside the PERSONAL.XLSB property.

Now, copy and paste the following script inside the blank Module and click Save:

Sub Auto_Open()
    Range("A1").Value = "Hello World"
End Sub

You can enter your VBA script between the Sub Auto_Open() and End Sub code lines. Alternatively, try the script given above for practice purposes.

Ensure you save the workbook as an XLSM file. The steps are available in the first method, outlined previously.

Now, close the workbook and reopen it to see that the macro created using the above method works, and it types Hello World as soon as you open the workbook.

Automatically executed a macro stored in the PERSONAL.XLSB property.

Run Macro Automatically in Newer Mac Versions

Running a macro automatically when opening Excel on newer Mac versions works a bit differently than on Windows, but the core concept is the same.

You embed your macro in a way that Excel knows to run it the moment the file opens. This is typically done by placing the macro inside the Workbook_Open() event in the ThisWorkbook module of a macro-enabled workbook.

Unlike Windows, Mac doesn’t have the Personal Macro Workbook available by default, and some features like Auto_Open() have limited support.

So, follow the steps mentioned in the first method to set up and run macros automatically when you open workbooks on a Mac.

Modify Excel Options Setting

Due to safety and privacy, all macro-enabled workbooks are stopped automatically by Excel from executing the active content, like a VBA script, on their own. It shows the Enable Content button. Upon clicking this button, the macro is executed. So, your intention to run a VBA script automatically when you open a workbook won’t serve if this feature is active. Find below the steps to disable this feature:

Open Excel and click on the File tab in the top-left corner to access the backstage view.

In the sidebar, scroll down and click Options to open the Excel Options window.

Inside the Excel Options window, click on Trust Center from the left-hand menu.

On the right side, click the Trust Center Settings button to open the Trust Center dialog box.

In the Trust Center, select Macro Settings from the list on the left pane

Choose Enable VAB macros if you want every macro to run automatically without any warning (not recommended for unknown files). So, do it, but don’t open Excel workbooks from unknown sources.

If you also want to control prompts for links or data connections, click on External Content and adjust those settings as needed.

Click OK to save your Trust Center changes, and then click OK again to exit the Excel Options window.

Conclusions

So, these are some of the popular and effortless ways to run a macro when opening Microsoft Excel.

Did you learn a new Excel skill today? Use the comment box to share your feedback about this Excel tutorial.

If you liked the tutorial, share it with your colleagues and friends as well.