This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

How to record macros in Excel Visual Basic Part four of a six-part series of blogs
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. How to Record Macros in Excel Visual Basic 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 This blog is part of our Excel macros online tutorial series - complemented, of course, by Wise Owl's excellent training courses in VBA and Excel for businesses like yours!

How to record macros in Excel Visual Basic

Part four of a six-part series of blogs

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.

How to Record Macros in Excel Visual Basic
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

This blog is part of our Excel macros online tutorial series - complemented, of course, by Wise Owl's excellent training courses in VBA and Excel for businesses like yours!

Posted by Andy Brown on 30 June 2011

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. 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!

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!

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!

Parts of this blog
How to Record Macros in Excel Visual Basic 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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR