Record-VBA-Macro

Excel allows you to automate some tedious tasks, using something called the Macro Recorder. As an example, what we have got here is some information from Years 1-10 and you'll see it goes from 100 in Year 1 to 1000 in Year 10. What we want to do is, next year, allow the 1000 to fall off at year 10 and the 100 to 900 must move one down- so there is space to enter the new information in Year 1. How we can do this is to use the Macro-Recorder and you will see if you click the "View" Tab and at the end you will find "Macros" -- you'll see the options are -- View Macros, Record Macros, and Use Relative References. I am going to Record a Macro and hence click on Record Macro -- you get some options, I am going to name it -- Rolling_Years- it is important to note that you can't have spaces in the name. You can give it a shortcut key if you want to. It is important to tell Excel where to store the Macro. It can either be in the Personal Macro Workbook which is a workbook that exists and which will open every time you open Excel, you can store it in a new workbook or else in "this workbook". I am going to choose this workbook. If you want you can give it a description. When I click ok, you'll see at the extreme bottom left hand side of the screen there is a little button, which warns you that a Macro is currently recording. When you want to stop, you will stop it on this button. But now any key strokes you type will be recorded so, for example, what I am going to do is cause the number column to roll down, so the first thing I am going to do is click on the 1000 opposite Year 10 and delete it. Then highlight cells 100 to 900 and copy the column, then click in the cell 200 and paste and then click in the top cell (100)- opposite Year 1- delete it and leave the highlighted cell opposite year 1 so that when the macro runs it will capture the data in this top cell. Switch off or stop the Macro. Now let's see how well the Macro works. Again under View, click Macros and go to View Macros and now because I have told Excel to show me only the Macros in this workbook, I will find "Rolling Years". Click "Run" and watch what happens. You will see it has deleted the bottom (900) taken the top and copied it down, so now you can insert 50 in year 1, 75 in year 2. The next year we go to View Macros, find Rolling Years, click "Run" and you'll see its moved down and slowly but surely It rolls all the way down. Now I want to show you what the recorder did while you were recording. If you go to Macros and go to View Macros and then go to Rolling Years and click on Edit -- it takes me to the VBA Editor and while you were recording key strokes, Excel was actually writing code.So you'll see here Range --choose Cell D21: Clear contents; Choose a range (D12:D20) copy it and paste it etc. So what it has done is it has taken your actions and turned it into code that Excel can understand. Also another useful ability in Excel, is once you have set up a Macro, you can actually set up a button or picture or symbol or something that you can use to automatically activate the Macro. For example, I am going to go to "Insert", and I am going to insert a shape. Place the shape in the worksheet as shown. If you right click on any shape or picture, you will find an option -- Assign Macro- click on it. It will give me all the Macros that are available in this workbook and I am going to click on "Rolling Years" and say ok. You can make changes to your shape -- make it neat and tidy and put words in it, etc. Click away from it. Now when I want to run the Macro- every time you click in the shape it will run and you will see the rolling down effect on the list of numbers -- by merely clicking in your shape you will be able to run your Macro very easily.

---

Closed Caption:

Excel allows you to automate some tedious
tasks, using something called the Macro Recorder.
As an example, what we have got here is some
information from Years 1-10 and you'll see
it goes from 100 in Year 1 to 1000 in Year
10. What we want to do is, next year, allow
the 1000 to fall off at year 10 and the 100
to 900 must move one down- so there is space
to enter the new information in Year 1. How
we can do this is to use the Macro-Recorder
and you will see if you click the "View"
Tab and at the end you will find "Macros"
-- you'll see the options are -- View
Macros, Record Macros, and Use Relative References.
I am going to Record a Macro and hence click
on Record Macro -- you get some options,
I am going to name it -- Rolling_Years- it
is important to note that you can't have
spaces in the name. You can give it a shortcut
key if you want to. It is important to tell
Excel where to store the Macro. It can either
be in the Personal Macro Workbook which is
a workbook that exists and which will open
every time you open Excel, you can store it
in a new workbook or else in "this workbook".
I am going to choose this workbook. If you
want you can give it a description. When I
click ok, you'll see at the extreme bottom
left hand side of the screen there is a little
button, which warns you that a Macro is currently
recording. When you want to stop, you will
stop it on this button. But now any key strokes
you type will be recorded so, for example,
what I am going to do is cause the number
column to roll down, so the first thing I
am going to do is click on the 1000 opposite
Year 10 and delete it. Then highlight cells
100 to 900 and copy the column, then click
in the cell 200 and paste and then click in
the top cell (100)- opposite Year 1- delete
it and leave the highlighted cell opposite
year 1 so that when the macro runs it will
capture the data in this top cell. Switch
off or stop the Macro. Now let's see how
well the Macro works. Again under View, click
Macros and go to View Macros and now because
I have told Excel to show me only the Macros
in this workbook, I will find "Rolling Years".
Click "Run" and watch what happens. You
will see it has deleted the bottom (900) taken
the top and copied it down, so now you can
insert 50 in year 1, 75 in year 2. The next
year we go to View Macros, find Rolling Years,
click "Run" and you'll see its moved
down and slowly but surely It rolls all the
way down. Now I want to show you what the
recorder did while you were recording. If
you go to Macros and go to View Macros and
then go to Rolling Years and click on Edit
-- it takes me to the VBA Editor and while
you were recording key strokes, Excel was
actually writing code.So you'll see here
Range --choose Cell D21: Clear contents;
Choose a range (D12:D20) copy it and paste
it etc. So what it has done is it has taken
your actions and turned it into code that
Excel can understand. Also another useful
ability in Excel, is once you have set up
a Macro, you can actually set up a button
or picture or symbol or something that you
can use to automatically activate the Macro.
For example, I am going to go to "Insert",
and I am going to insert a shape. Place the
shape in the worksheet as shown. If you right
click on any shape or picture, you will find
an option -- Assign Macro- click on it. It
will give me all the Macros that are available
in this workbook and I am going to click on
"Rolling Years" and say ok. You can make
changes to your shape -- make it neat and
tidy and put words in it, etc. Click away
from it. Now when I want to run the Macro-
every time you click in the shape it will
run and you will see the rolling down effect
on the list of numbers -- by merely clicking
in your shape you will be able to run your
Macro very easily.