Recording your Macro and Finishing

Once you've started recording a macro, Excel (or the Bob/Bobette inside your computer) will transcribe everything you do into Visual Basic, until you stop recording.  For this reason, you should proceed carefully!

For our example, here's what you should do. 

Step 1 - Copying each Name

Remembering that you are recording, you should follow these steps exactly to make your recorded macro do what you want:

• First, click on the first cell to be copied (C4 on the Input Form worksheet), and copy it:

Right-click on each cell to be copied, and choose to copy it using the short-cut menu (as here) or any other way

• Now go to the top of the list of names in the Results sheet:

Select the top "person" in the list of names

You now want to select the first non-blank cell in the list.  To do this, you want to record the instructions:

• Go down to the bottom of the block; then
• Go down one cell further

rather than the instructions:

• Go to cell B8 (which will always take you back to the same cell, whenever you run the macro)

You therefore need to switch from absolute to relative recording.

Switching to Relative Recording

To change the way Excel records macros:

1. Choose the Developer tab on the ribbon (if you can't see this, see below).
2. Choose to use relative references.

If you can't see the Developer tab, see below

In Excel 2003, you can accomplish the same thing by clicking on the tool shown (this only appears once you have started recording).

If you can't see the Developer tab on your ribbon, you need to enable it:

• For how to do this in Excel 2010, see this separate blog. 
• For how to do this in Excel 2007, read on!

If you're using Excel 2007, first choose to show Excel options:

You can now show the Developer tab in the ribbon:

Tick the box shown to display the Developer tab on your Excel ribbon

Pasting the Names

You can now paste in the name you've copied:

Go to the top of the column of names	Press CTRL + the down arrow to go to the bottom of the block, then press the down arrow again to go to the first blank cell

Formatting the Results

If you patiently repeat the steps above, you will eventually build up a row of copied data:

The results of copying and pasting - the right data, but with the wrong formatting

You can now format the cells using the standard formatting tools so that:

• the first cell is in a larger font;
• the other cells are vertically aligned centrally; and
• the whole row has an outline border.

Here's what the final result could look like:

The final result, correctly formatted

You can now stop recording!

Stopping Recording

To stop recording, click on the tool shown below (in Excel 2003 this tool is on its own toolbar, which appears when you start recording).

Click on the button shown to stop recording

You should now have a macro which copies a contestant's score to the results sheet.  It's time now to tidy it up!