This is a follow on from our previous VBA tip on how to record macros.

You can record a macro and get it to work without having to understand the code at all, and that is very useful for automation of simple tasks. The code can get quite long though, and if it gets too big, it becomes unwieldy. At this point it is a good idea to clean it up.

The best way to do this is to understand a few basics, and to be able to condense many lines of code into a few.
Lets see this in action for a real life example:

We have a feed coming into our Excel, and it brings in the latest share price.

Suppose we want to take a snapshot of that each day at the end of the day and then use that to calculate the daily change.
Let’s record what we would do to save that share price daily.

Step1:
To record a macro, press the following in sequence:
ALT then t then m then r
That will bring up this screen:

Step2:
Press OK, and now start the actions you would do to save the price daily.
- Select B2, press CTRL and C (to copy)
- Select A2
- Press ALT then e then s then v then ENTER (to paste special values)

Now, the daily change will be reset to zero, and it will start calculating the next day's daily change when you get in tomorrow.

Step3:
Stop the macro recording:

Step4:
Now press ALT and F11, and look what the macro recorder has recorded.

It will look something like this:

Now you attach the code to a button using our first VBA lesson, and press it every day when you want to run it.

NOW... here’s the important part of the lesson.
The macro recorder has recorded the following:
Range("B2").Select
- This is the action of selecting B2

Selection.Copy
- This is the action of copying the selection

Range("A2").Select
- This is the action of selecting A2

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
- This is the action of pasting only the values (not the formatting or the formula) of the cell B2 onto A2

This entire code can be condensed into one line:
Range("A2").Value = Range("B2").Value

NOTE – there’s no need to select anything... we can just set the value of cell A2 to equal the value of B2 !

Download sheet to practise recording macros

Video training on: What Does the recorded code mean ?