Microsoft enables us to automate some of the routine actions that we want to do in our Excel worksheets and Word documents. Of course, the process isn’t quite consistent between Excel and Word. In this article, I’ll focus on creating a macro in Excel 2013.
First, we need to make the macro options available to us. Excel hides them on the Developer tab, which is not shown by default.
To display the Developer tab, click on File, then click on Options. This will open the Excel Options dialog box.
Click on Customize Ribbon in the left column, and then put a check in the checkbox beside Developer on the right. If you don’t see the Developer option, make sure that the "Customize the Ribbon" option selects "Main Tabs." Then click OK.
Now we have the Developer tab displayed in the Ribbon. On the left side of the Developer tab, we see the "Code" section, which includes links for our macro options.
In the image below, notice the Macros icon just to the left of the Record Macro link (which becomes a Stop Recording link when you’re recording your macro) — we’ll use the Macros icon in a minute…
Click the Record Macro link…
Now is the time when we step through links, selections and other parts of the process that we want to automate via the macro.
Later, we’ll create a button in the worksheet and assign the macro to it for easy use.
When you’ve done the steps that you want to include in the Excel macro, return to the Developer tab and click on Stop Recording.
Once we have recorded our macro, we can display the generated code so that we can review it. We can also edit/change it, if we like, rather than having to re-record it for changes.
Click on the Macros icon to open the code editor and display the macro you’ve recorded.
In this case, I created a spreadsheet with all data inputs on sheets Sheet1, Sheet2 and Sheet3. I created a macro to select each block of data and then print it.
Below is the code generated for the macro.
Notice that the macro is printing specific cell ranges in each sheet. This means the macro would have to be edited any time data is added in additional cells.
There’s an easy way to simplify this issue. If you name ranges of cells, you can print the ranges.
That way, if you add more data, you just change the range to which the name applies. The macro would be structured differently, as shown below.
Once we have our macro recorded, we’re almost there…
We need to create a button to trigger the macro. Move to the location you want to have the button.
Click on the Developer tab. Then click on the Insert icon and select the Button from the Form Controls section.
Once we have the button created (you can see my finished version in the image, labelled "Print Seasons 1-3"), all we need to do is link the macro to the button.
Right-click on the button. From the pop-up options menu, select Assign Macro…
On the resulting Assign Macro dialog box, select the macro that you want to use. In this case, I selected PrintByName. Then click OK.
At that point, we’ve created the macro and then created a button to trigger the macro.
Clicking the macro will execute the macro’s steps.