Microsoft Excel: Record macros to quickly perform routine tasks

Q. I use an Excel workbook that contains 14 worksheets. A different number of copies of each worksheet must be printed, but the entire workbook must be printed in one step. Is there any way to do this?

A. If you have simple tasks that you need to perform in Excel often, you can actually “teach” Excel to do them for you by recording a macro. Many times, when I use the word “macro,” people are immediately turned off because they assume it will be complicated and require them to use coding. But simple macros can be created in Excel that require no coding, and they can be used repeatedly to perform many different tasks.

Based on the question, I have created a simplified workbook to illustrate. Click here to download the workbook and watch the video walkthrough at the bottom of the page.

While macros can be created using code, they can also be created by having Excel record your actions. It is very simple. I illustrate, in the accompanying video, how to record a macro in Excel using the situation described in the question. However, you can record macros to perform a wide variety of tasks in Excel.

To access macros, you need to add the Developer tab to your Ribbon if it is not already there. Add this tab to the Ribbon by going to File, Options, Customize Ribbon. Under Customize the Ribbon:, select Main Tabs and put a checkmark next to Developer. Click OK. The Ribbon should look similar to the one in the screenshot below.

Go to the Developer tab on your Ribbon and click Record Macro from the Code group. The Record Macro window will open. Choose a name for your macro and enter it next to Macro name:. The name cannot have any spaces.

Then, optionally, choose a Shortcut Key and enter it next to Shortcut key:, Ctrl+. If you choose a shortcut that already exists, Excel will change Ctrl+ to Ctrl+Shift+.

Under Store macro in:, choose where you want to store the macro. If you will be running the macro within this same workbook every time, choose This Workbook. However, if you will be running the macro on different workbooks, chose Personal Macro Workbook. If you choose This Workbook to store the macro, the workbook will have to be saved as an Excel Macro-Enabled Workbook (*xlsm). Excel will prompt you if this is the case. Also, if you share the file with someone else, he or she can also use the macro.

However, if you choose Personal Macro Workbook to store the macro, you will not have to save it as an Excel Macro-Enabled Workbook (*xlsm), and the macro will not be shared with others when you share the file. The best option depends on your needs.

Finally, enter a Description, if you like. Click OK. See the screenshot below for the options I chose for the example.

Next, perform the actions you would like Excel to perform. Every action you take will be recorded by Excel and stored in the macro. Every time the macro is run, Excel will repeat each and every action that you did. Be sure you perform every action as you want Excel to repeat it. If you make a mistake, you can start again. Once you are finished performing all actions to be recorded, go to the Developer tab on your Ribbon and click Stop Recording from the Code group.

Now that you have created your macro, you can run it on Excel workbooks or worksheets. Simply open a workbook where you would like Excel to perform the recorded task(s). Go to the Developer tab on your Ribbon and click Macros from the Code group. Select the macro you want to run and click Run. Alternatively, you can use the shortcut key if you created one. Excel will perform all of the recorded actions to the workbook. Below, you can view the video demonstration of the macro being recorded for the example in this article.

About the author

Kelly L. Williams, CPA, Ph.D., MBA, is an assistant professor of accounting at Middle Tennessee State University.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.