Take a look at the pivot table shown in Figure 11.1. You know that you can refresh this pivot table by right-clicking inside the pivot table and selecting Refresh Data. Now if you were to record your actions with a macro while you refreshed your pivot table, you, or anyone else, could replicate your actions and refresh this pivot table by running the macro.
Figure 11.1. Recording your actions while refreshing this pivot table will allow you to simply run a macro the next time you have to refresh.
If you are using Excel 2000 or a later version, your macro security settings may be set to High, effectively disabling all macros. In order to utilize macros, you will have to set your security settings to Medium or Low. Go up to the application menu and select Tools, Macro, Security. It's generally a good rule to set the macro security level to Medium. This will allow macros to run, only after you explicitly enable them when you open Excel.
The first step in recording a macro is to initiate the Record Macro dialog box. Go up to the application menu and select Tools, Macro, Record New Macro.
When the Record Macro dialog box activates, you will fill in a few key pieces of information about the macro:
Because this macro will refresh your pivot table when it is played, you will name your macro RefreshData. You will also assign a shortcut key of R. You will notice that the dialog box, shown in Figure 11.2, gives you a shortcut key of Ctrl+Shift+R. Keep in mind that you will use the shortcut key to play your macro once it is created. Be sure to store the macro in This Workbook. Click OK to continue.
Figure 11.2. Fill in the Record Macro dialog box as shown here.
When you click OK from the Record Macro dialog box, you initiate the recording process. At this point, any action you perform is recorded by Excel. That being the 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 application menu and selecting Tools, Macro, Stop Recording.
Congratulations! You have just recorded your first macro. You can now play your macro by pressing Ctrl, Shift, and R on your keyboard at the same time.