Recording an Example Macro

The previous section described the general steps for recording a macro. This section shows you a step-by-step example of recording a short macro that we'll open and edit in the Visual Basic Editor later in the chapter.

The macro simply copies the active worksheet to an archive workbook so that you have a reference copy of the worksheet. To copy the worksheet, the macro takes five actions:

• Opens the archive workbook.
• Activates the previous workbook.
• Copies the worksheet to the archive workbook.
• Saves the archive workbook.
• Closes the archive workbook.

To record the example macro, follow these steps:

1. Create a workbook named Archive.xlsx, then close it. For example:
   1. Press Cmd+N to create a new blank workbook.
   2. Press Cmd+S to display the Save As dialog box.
   3. Choose the folder in which you want to save the workbook.
   4. Type the name Archive.
   5. Click the Save button.
   6. Press Cmd+W to close the workbook.
2. Open a workbook that contains a worksheet you'd like to copy to the archive workbook. Make that worksheet active.
3. Choose Developer Visual Basic Record from the Ribbon or Tools Macro Record New Macro to display the Record Macro dialog box.
4. In the Macro name text box, type the name Copy_Active_Worksheet_to_Archive_Workbook.
5. Click in the Shortcut key text box and press Shift+C to enter a capital C, which will create the keyboard shortcut Cmd+Option+Shift+C for the macro.
6. In the Store macro in pop-up menu, select Personal Macro Workbook.
7. In the Description text box, type the description for the macro: Copies the active worksheet to the Archive.xlsx workbook.
8. Click the OK button to close the Record Macro dialog box. The Macro Recorder starts recording the macro, and the Recording readout appears in the status bar.
9. Record the action for opening the archive workbook:
   1. Press Cmd+O to display the Open: Microsoft Excel dialog box.
   2. Navigate to and select the Archive.xlsx workbook.
   3. Click the Open button. The workbook opens as usual and becomes the active workbook.
10. Press the Cmd+Shift+F6 keyboard shortcut to switch to the previous workbook—the one that contains the worksheet you want to copy.
11. Record the action for copying the worksheet to the archive workbook:
    • Ctrl-click or right-click the active worksheet's tab, then click Move or Copy on the context menu to display the Move or Copy dialog box.
    • Open the To book pop-up menu, then click Archive.xlsx.
    • In the Before sheet list box, click the “(move to end)” item.
    • Select the Create a copy check box.
    • Click the OK button to close the Move or Copy dialog box. Excel copies the worksheet to the archive workbook and makes that workbook active.
12. Press Cmd+S to save the archive workbook.
13. Press Cmd+W to close the archive workbook.
14. Choose Developer Visual Basic Record from the Ribbon or Tools Macro Stop Recording to stop the Macro Recorder.