Macro Recorder

This is a simple, but practical way to get your feet wet learning Excel macros.  You can use them to build a clickable table of contents for your workbook, such as for a financial package.

The exercise is in first file, Index Builder.  The result is the Index Builder Result file, the second file listed.  Open up the exercise file. 

Index Builder Exercise

Index Builder Result

From the menu bar select:

• Tools
• Macros
• Record New Macro
• When the dialog box comes up, give it a name that will mean something.  In this case, call it SelectFirst (what we are doing is building a macro that will select the First sheet). 
• Click OK

The macro recorded is turned on and ready to record your work. Here is what you do to finish the recording:

• Select the First sheet.
• Click the Stop Recording button. 

Now let's see what you recorded. 

• Select Tools, then Macro, then Macros.
• In the dialog box, highlight the macro we just did, SelectFirst, then select Edit.

Here is the code you will see for SelectFirst

Sub SelectFirst()
'
' SelectFirst Macro
' Macro recorded 2/9/2003 by Jon Paul
'

'
    Sheets("First").Select
End Sub

The parts of the code are:

• Title.  Always Sub then followed by the name.
• Comment section- lines that start with " ' ".  This is informational material, that does not affect the program.  Here you can put in comments to help you understand the code.
• The code.  In this case, just one line.
• Closing.  Always End Sub

To test how the code runs, do this:

• Go back to the workbook
• Select another sheet
• Then select, Tools, Macro, Macros.  Highlight the SelectFirst code.  This time select Run.

You should now be taken to the First sheet.

In part two of the lesson, we will show you how to duplicate this code and complete your table of contents.

Return To Excel Tips Index