Recording a Second Macro:

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

The 2003 worksheet appears.

Let's begin by enabling Excel's macro recorder.

Step2. To turn on the macro recorder, in the status bar,

Click

NOTE for MacOS Users: To turn on the macro recorder, in the status bar,
Click

We see the Record Macro dialog box.

Step3. To complete the setup for this Macro, input the following data into the appropriate areas in the Record Macro dialog box:

We are ready to begin recording.

Step4. To begin recording,

Click

Let's take a look at the actions we will record.

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.

Sorting

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

We didn't need to select the whole range because Excel will automatically select all adjacent cells that contain data.

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,

Click

You see the Sort dialog box:

Step3. To change the Sort by option to Expense, on the Sort by column,

Click, Click Expense

This is our only sort criteria.

Step4. To complete the sorting process,

Click

The table is sorted by Expense.

Calculating Subtotals

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,

Click

You see the Subtotal dialog box:

We want Excel to calculate the Sum total in the Amount field every time the expense category changes. To do this, we will select the appropriate settings.

Step2. Change the dialog box settings shown below:

The Subtotal dialog box should look like:

NOTE: You will have to deselect the Vendor checkbox.

Step3. To confirm these settings as correct,

Click

We see that our data is sorted by expense type and each expense group has its own subtotal.

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,

Click

The actions have been recorded as the content of the DataSorter macro.

You see subtotals displayed along with your data. Numbered buttons () also appear in the worksheet's far left column. Button 1 will display the Grand Total, button 2 will display Subtotals, and button 3 will show all data.

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

The 2004 worksheet appears.

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.

We see an error message:

Excel is telling us that if we continue, the 2003 worksheet will be reset, causing all our sorting and subtotaling in the 2003 worksheet to be removed. This error is caused by the way the macro is translated to VBA code. We don't want to do this because it is a sign that something in our macro isn't functioning as we wanted it to. We can't do anything about it now, but we will see how to fix this problem in the next section.

Step3. To acknowledge the error and stop the macro,

Click

We now see a Visual Basic runtime error message:

4. To bypass this error,

Click

NOTE for MacOS Users: To bypass this error, Click.

We will work on fixing this error in a few minutes, but to do so, we need to become acquainted with the Visual Basic Editor and how macro code is structured.