12. Enhancing Pivot Table Reports with Macros

In its broadest sense, a macro is a sequence of instructions that automates some aspect of Excel so that you can work more efficiently and with fewer errors. Macros can be created by recording and saving a series of keystrokes. Once saved, a macro can be played back on demand. In other words, you can record your actions in a macro, save the macro, and then allow your clients to play back your actions with the click of a button or press of a keyboard shortcut. It would be as though you were right there with them! This functionality is especially useful when you’re distributing pivot table reports.

For example, suppose that you want to give your clients the option of grouping their pivot table report by month, by quarter, or by year. Although the process of grouping can be performed by anyone, some of your clients might not have a clue how to do it. In this case, you could record a macro to group by month, a macro to group by quarter, and a macro to group by year. Then you could create three buttons, one for each macro. In the end, your clients, having little experience with pivot tables, need only to click a button to group their pivot table report.

A major benefit of using macros with your pivot table reports is the power you can give your clients to easily perform pivot table actions that they would not normally be able to perform on their own, empowering them to more effectively analyze the data you provide.

Figure 12.1 You can easily refresh this basic pivot table by right-clicking and selecting Refresh, but if you recorded your actions with a macro, you could also refresh this pivot table simply by running the macro.

The first step in recording a macro is to initiate the Record Macro dialog. Select the Developer tab on the ribbon and then select Record Macro.

When the Record Macro dialog activates, you can fill in a few key pieces of information about the macro:

Macro Name—Enter a name for your macro. You should generally enter a name that describes the action being performed.

Shortcut Key—You can enter any letter into this input box. That letter becomes part of a set of keys on your keyboard that can be pressed to play back the macro. This is optional.

Store Macro In—Specify where you want the macro to be stored. If you are distributing your pivot table report, you should select This Workbook so that the macro is available to your clients.

Description—In this input box, you can enter a few words that give more detail about the macro.

Because this macro refreshes your pivot table when it is played, name your macro RefreshData, as shown in Figure 12.2. Also assign the shortcut key R. Notice that, based on this, the dialog gives you the key combination Ctrl+Shift+R. Keep in mind that you use the key combination to play your macro after it is created. Be sure to store the macro in This Workbook. Click OK to continue.

Figure 12.2 Fill in the Record Macro dialog as shown here and then click OK to continue.

When you click OK in the Record Macro dialog, you initiate the recording process. At this point, any action you perform is recorded by Excel. In this case, you want to record the process of refreshing your pivot table.

Right-click anywhere inside the pivot table and select Refresh Data. After you have refreshed your pivot table, you can stop the recording process by going up to the Developer tab and selecting the Stop Recording button.

Congratulations! You have just recorded a macro. You can now play your macro by pressing Ctrl, Shift, and R on your keyboard at the same time.

A basic user interface provides the perfect solution. A user interface is a set of controls such as buttons, scrollbars, and other devices that allow users to run macros with a simple click of the mouse. In fact, Excel offers a set of controls designed specifically for creating user interfaces directly on a spreadsheet. These controls are called form controls. The general idea behind form controls is that you can place one on a spreadsheet and then assign a macro to it—meaning a macro you have already recorded. After a macro is assigned to the control, that macro is executed, or played, when the control is clicked.

You can find form controls in the Controls group on the Developer tab. To get to the form controls, simply select the Insert icon in the Controls group, as shown in Figure 12.3.

Figure 12.3 To see the available form controls, click Insert in the Controls group on the Developer tab.

Here, you can select the control that best suits your needs. In this example, you want your clients to be able to refresh their pivot table with the click of a button. Click the Button control to select it and then drop the control onto your spreadsheet by clicking the location you would like to place the button.

After you drop the button control onto your spreadsheet, the Assign Macro dialog, shown in Figure 12.4, opens and asks you to assign a macro to this button. Select the macro you want to assign to the button, in this case RefreshData, and then click OK.

Figure 12.4 Select the macro you want to assign to the button, and then click OK. In this case, you want to select RefreshData.

As you can see in Figure 12.5, you can assign each macro in your workbook to a different form control and then name the controls to distinguish between them.

Figure 12.5 You can create a different button for each one of your macros.

As you can see in Figure 12.6, when you have all the controls you need for your pivot table report, you can format the controls and surrounding spreadsheet to create a basic interface.

Figure 12.6 You can easily create the feeling of an interface by adding a handful of macros, a few form controls, and a little formatting.

To get a better understanding of how this process works, start by creating a new macro that extracts the top five records by customer. Go to the Developer tab and select Record Macro. Set up the Record Macro dialog as shown in Figure 12.7. Name your new macro TopNthCusts, and specify This Workbook as the place where you want to store the macro. Click OK to start recording.

Figure 12.7 Name your new macro and specify where you want to store it.

After you have started recording, right-click the Customer field and select Filter. Then select Top 10. Selecting this option opens the Filter dialog, where you specify that you want to see the top five customers by sales amount. Enter the settings shown in Figure 12.8 and then click OK.

Figure 12.8 Enter the settings you see here to get the top five customers by revenue.

After you record the steps to extract the top five customers by revenue, select Stop Recording from the Developer tab.

You now have a macro that, when played, filters your pivot table to the top five customers by revenue. The plan is to tweak this macro to respond to a scrollbar. That is, you force the macro to base the number used to filter the pivot table on the number represented by a scrollbar in your user interface. In other words, a user can get the top 5, top 8, or top 32 simply by moving a scrollbar up or down.

Right-click the scrollbar and select Format Control. This activates the Format Object dialog, in which you make the following setting changes: Set Minimum Level to 1 so the scrollbar cannot go below 1, set Maximum Level to 200 so the scrollbar cannot go above 200, and set Cell Link to $M$2 so that the number represented by the scrollbar will output to cell M2. After you have completed these steps, your dialog should look like the one shown in Figure 12.9.

Figure 12.9 After you have placed a scrollbar on your spreadsheet, configure the scrollbar as shown here.

Next, you need to assign the TopNthCusts macro you just recorded to your scrollbar. To do this, right-click the scrollbar and select Assign Macro. Select the TopNthCusts macro from the list shown in Figure 12.10 and then click OK. Assigning this macro ensures that it plays each time the scrollbar is clicked.

Figure 12.10 Select the macro from the list.

At this point, test your scrollbar by clicking it. When you click your scrollbar, two things should happen: The TopNthCusts macro should play, and the number in cell M2 should change to reflect your scrollbar’s position. The number in cell M2 is important because that is the number you are going to reference in your TopNthCusts macro.

The only thing left to do is tweak your macro to respond to the number in cell M2, effectively tying it to your scrollbar. To do this, you have to get to the VBA code that makes up the macro. You have several ways to get there, but for the purposes of this example, go to the Developer tab and select Macros. Selecting this option opens the Macro dialog, exposing several options. From here, you can run, delete, step into, or edit a selected macro. To get to the VBA code that makes up your macro, select the macro and then click Edit, as demonstrated in Figure 12.11.

Figure 12.11 To get to the VBA code that makes up the TopNthCusts macro, select the macro and then select Edit.

The Visual Basic Editor opens, with a detailed view of all the VBA code that makes up this macro (see Figure 12.12). Notice that the number 5 is hard-coded as part of your macro. The reason is that you originally recorded your macro to filter the top five customers by revenue. Your goal here is to replace the hard-coded number 5 with the value in cell M2, which is tied to your scrollbar. Therefore, you need to delete the number 5 and replace it with the following:

Click here to view code image

ActiveSheet.Range("M2").Value

Figure 12.12 Your goal is to replace the hard-coded number 5, as specified when you originally recorded your macro, with the value in cell M2.

Your macro’s code should now look like the code shown in Figure 12.13.

Figure 12.13 Simply delete the hard-coded number 5 and replace it with a reference to cell M2.

Close the Visual Basic Editor to get back to your pivot table report. Test your scrollbar by setting it to 11. Your macro should play and filter out the top 11 customers by revenue, as shown in Figure 12.14.

Figure 12.14 After a little formatting, you have a clear and easy way for your clients to get the top customers by revenue.

---

Case Study: Synchronizing Two Pivot Tables with One Combo Box

The report in Figure 12.15 contains two pivot tables. Each pivot table has a filter field for allowing you to select a market. The problem is that every time you select a market from the filter field in one pivot table, you have to select the same market from the filter field in the other pivot table to ensure that you are analyzing the correct units sold versus revenue.

Figure 12.15 This pivot table report contains two pivot tables with filter fields that filter out a market. The issue is that you have to synchronize the two pivot tables when analyzing data for a particular market.

Not only is it a bit of a hassle to have to synchronize both pivot tables every time you want to analyze a new market’s data, but there is a chance that you or your clients might forget to do so.

One way to synchronize these pivot tables is to use a combo box. The idea is to record a macro that selects a market from the Market field of both tables. Then you can create a combo box and fill it with the market names that exist in your two pivot tables. Finally, you can alter your macro to filter both pivot tables, using the value from your combo box. To do so, follow these steps:

1. Create a new macro and call it SynchMarkets. When recording starts, select the California market from the Market field in both pivot tables; then stop recording.

2. Activate the Forms toolbar and place a combo box onto your spreadsheet.

3. Create a hard-coded list of all the markets that exist in your pivot table. Note that the first entry in your list is (All). You must include this entry if you want to be able to select all markets with your combo box.

As you can see in Figure 12.16, you place the combo box and your list of markets directly in your spreadsheet.

Figure 12.16 At this point, you should have all the tools you need: a macro that changes the Market field of both pivot tables, a combo box on your spreadsheet, and a list of all the markets that exist in your pivot table.

4. Right-click your combo box and select Format Control to perform the initial setup. First, specify an input range for the list you are using to fill your combo box—in this case, the market list you created in step 3. Next, specify a cell link—that is, the cell that shows the index number of the item you select (cell H1 in this example). After you have configured your combo box, your dialog should look like the one shown in Figure 12.17.

Figure 12.17 The settings for your combo box should reference your market list as the input range and specify a cell link close to your market list. In this case, the cell link is cell H1.

At this point, you should be able to select a market from your combo box and see the associated index number in cell H1. Why an index number instead of the name of the selected market? Well, the only output of a combo box form control is an index number. This is the position number of the selected item. For instance, in Figure 12.18, the selection of Charlotte from the combo box results in the number 5 in cell H1. This means that Charlotte is the fifth item in the combo box.

Figure 12.18 Your combo box, now filled with market names, will output an index number in cell H1 when a market is selected.

To make use of this index number, you have to pass it through the INDEX function. The INDEX function converts an index number to a value that can be recognized.

5. Enter an INDEX function (shown in Figure 12.19) that converts the index number in cell H1 to a value.

Figure 12.19 The INDEX function in cell I1 converts the index number in cell H1 to a value. You will eventually use the value in cell I1 to alter your macro.

An INDEX function requires two arguments to work properly. The first argument is the range of the list you are working with. In most cases, you use the same range that is feeding your combo box. The second argument is the index number. If the index number is in a cell (for example, in cell H1), you can simply reference the cell.

6. Edit the SynchMarkets macro using the value in cell I1 instead of a hard-coded value.

To get to the VBA code that makes up your macro, click the Macros button on the Developer tab. This activates the Macro dialog, shown in Figure 12.20. From here, select the SynchMarkets macro and then click Edit.

Figure 12.20 In the Macro dialog, select the SynchMarkets macro and click Edit.

When you recorded your macro originally, you selected the California market from the Market field in both pivot tables. As you can see in Figure 12.21, California is hard-coded in your macro’s VBA code.

Figure 12.21 The California market is hard-coded in your macro’s VBA code.

Replace California with ActiveSheet.Range(“I1”).Value, as shown in Figure 12.22. This code references the value in cell I1. After you have edited the macro, close the Visual Basic Editor to get back to the spreadsheet.

Figure 12.22 Replace California with ActiveSheet.Range("I1").Value, and then close the Visual Basic Editor.

7. All that is left to do is ensure that the macro will play when you select a market from the combo box. Right-click the combo box and select Assign Macro. Select the SynchMarkets macro and then click OK.

8. Clean up the formatting on your newly created report by hiding the filter fields in your pivot tables, the market list you created, and any unseemly formulas.

As you can see in Figure 12.23, this setup provides your clients with an attractive interface that allows them to make selections in multiple pivot tables using one control.

Figure 12.23 Your pivot table report is ready to use!

---