Читать книгу Excel Macros For Dummies - Dick Kusleika - Страница 30

Macros can be used with any Excel object normally used in reporting. For instance, you can use a macro to give your users a way to dynamically change aPivotTable. In the example shown in Figure 1-11, macros allow a user to change the perspective of the chart simply by clicking any one of the buttons.

Figure 1-12 reveals that the chart is actually a PivotChart tied to a PivotTable. The recorded macros assigned to each button are doing nothing more than rearranging the PivotTable to slice the data using various pivot fields.

FIGURE 1-11: This report allows users to choose their perspective.

FIGURE 1-12: The macros behind these buttons rearrange the data fields in a PivotTable.

Here are the high-level steps needed to create this type of setup:

1 Create your PivotTable and PivotChart.

2 Start recording a macro.

3 While recording, move a pivot field from one area of the PivotTable to the other. When you’re done, stop recording the macro.

4 Record another macro to move the data field back to its original position.

5 After both macros are set up, assign each one to a separate button.

You can run your new macros in turn to see your pivot field dynamically move back and forth.