We will now record a second, more complex, macro to showcase some of the power behind Excel's macro interface. The macro we work with will take the data in our workbook, sort it into categories, and provide us subtotals based on our expense type. This type of information is helpful when determining what areas of our business could become more cost effective. At the end of every year, we will be compiling this information and placing it in a workbook file. Our current workbook contains information from years 2003-2006. In the future, we might put this information in a separate workbook just to keep things more organized.
We will begin our recording on the 2003 worksheet.
Step1. To select the 2003 worksheet,
Click the 2003 worksheet tab
Step2. To turn on the macro recorder, in the status bar,
NOTE for MacOS Users: To turn on the macro recorder, in the status bar,
Step3. To complete the setup for this Macro, input the following data into the appropriate areas in the Record Macro dialog box:
|Dialog Box Labels||Data to Enter|
|Store macro in:||This Workbook|
|Description||Sort data and return subtotals by Expense type|
Step4. To begin recording,
Recording a Sequence of Actions
Our data may or may not be the same size on each workbook, so we will be working to make this macro as scalable as possible. Unfortunately for us, we won't be able to make our data flexible using just a macro; we will have to enter the Visual Basic Editor to slightly modify the code Excel generates when we are finished recording.
To find the subtotals of each expense class, we must first sort the data in expense order. The first step in this process is to choose the active cell. If we select an active cell in the list as the first action, the macro will always begin using that particular cell, regardless of where the active cell is when the macro is triggered. We only need to select a cell in the range because Excel will automatically select all adjacent cells when the sort function is used.
Step1. To select a cell in the list,
Click cell A10
Step2. To begin to activate the Sort command, on the Ribbon,
Click the Data tab, Click
NOTE for MacOS Users: To begin to activate the Sort command, on the Ribbon,
Step3. To change the Sort by option to Expense, on the Sort by column,
Click, Click Expense
Step4. To complete the sorting process,
Now we can total the expense groups using the Subtotal tool. The Subtotal tool in Excel will summarize data in a sorted list. Each column should contain the same data type and have an assigned label like the first row of our data. We will create subtotals to show how much we are spending in each category listed in the worksheet.
Step1. To select the Subtotals option, in the Outline group on the right side of the Data tab,
Step2. Change the dialog box settings shown below:
|At each Change in:||Expense|
|Add Subtotal to:||Amount|
NOTE: You will have to deselect the Vendor checkbox.
Step3. To confirm these settings as correct,
Finishing Recording the Macro
We will finish the macro by leaving cell A10 as the active cell.
Step1. To stop the recorder, in the status bar,
Step2. Save macroex.xlsm.
Running the Macro
We saw earlier the multiple ways to run a macro. For this exercise, we will simply use the keyboard shortcut to sort and subtotal our data.
Step1. To sort the 2004 worksheet,
Click the 2004 worksheet tab
Step2. To run the macro, in the Ribbon, on the View tab,
Click, Click DataSorter, Click
NOTE for MacOS Users: To run the macro, in the Ribbon, on the View tab,
Click, Click DataSorter, Click.
Step3. To acknowledge the error and stop the macro,
4. To bypass this error,
NOTE for MacOS Users: To bypass this error, Click.