BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
If you're not sure what the Excel Visual Basic for Applications language is (or how it can help you) this blog attempts to explain! Learn what VBA is, and how you can record macros and then replay them again and again to automate work in Excel.
- Excel VBA Macros - Free On-Line VBA training
- What is Excel VBA, and how can you use it?
- Starting to Record Macros in Excel VBA
- Recording your Macro and Finishing (this blog)
- Tidying up your macro that has been recorded
- Playing back your macro recorded in Excel VBA
Posted by Andy Brown on 30 June 2011
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
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:
- Choose the Developer tab on the ribbon (if you can't see this, see below).
- 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
Switching between absolute and relative recording is one of the few things for which Excel will not record commands.
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!
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!
You could omit this last stage, but it's likely that you made several mistakes in the recording process which you'll need to rectify, and it can't do any harm to check what you've done!