VBA Macros – How to create Your Own Excel Macros

Excel VBA macros make repetitive tasks as simple as recording your keystrokes and pressing run. This tutorial will show you how to access the macro recorder, how to record your macros and how to run your macros to remove repetition from your daily task list. This tutorial is a fairly basic tutorial and therefore requires only a basic knowledge of Excel. To learn the basics, Excel 2013 For Dummies Video Training, Deluxe Edition course offers 106 lectures and 19.5 hours of content that will teach you the basics of Excel 2013. The course will teach you to launch and navigate worksheets as well as how to save and print your data. You will learn how to enter data and how to use AutoFill to make data entry simpler. The course will teach you to enter and print formulas and once you have a good knowledge of the basics the course will teach you advanced functions like how to work with pivot charts and tables. The course also covers how to share your workbooks via SkyDrive.

Activate the Developer Tab

The “Record Macro” option can be found on the Developer Tab in Excel. The Developer Tab is not necessarily visible in Excel. If you do not see a Developer Tab on your ribbon, then you need to activate the Developer Tab in your Excel 2013 application. To activate the Developer Tab, click on “File” and then select the options section. Select the “Customize Ribbon” option and then make sure “Developer” has a check mark next to it. If it does not have a check mark then click it to add a check mark and then select OK. This will add the Developer Tab to your Excel ribbon.

We will use a macro to move data around on our worksheet. Here is our initial data:

For the purposes of this tutorial, we will add lines below each name and then move the address data to below each name. This will allow us to print out the information in the form of address labels. This is what we want our completed spreadsheet to look like:

You could spend your time copying and pasting the information, but if you have an address list of a few hundred names this could take hours, if not days. We will create a simple macro to copy and paste for us.

Record a Macro

To record a macro, you need to select the option under the Developer Tab. Before we record our macro, we need to select “Use relative references.”

A macro records the keystrokes you make on your computer. Excel records each keystroke and saves all the keystrokes as a macro. To understand the difference between relative and absolute references, take a look at the following set of instructions:

Set 1:

Start at A. Move down one step. Move forward one step. End at B2

Set 2:

Start at first position. Move down one step. Move forward one step. End at position one step from start.

In set 1, the computer will always start at A and repeat the steps. In set 2, the computer will start at the first position and end at a position relative to the first position.

This is an example of relative references. For our macro, we want to use relative positions because we want to move the data relative to the names in the sheet and where the macro ends each time.

To record a macro, click “Record Macro” on the Developers Tab. A record macro window will open. Name your macro. In this case we will call our macro “Address” and we will assign “a” to the shortcut. Your record macro window should look like this:

Click OK to begin recording your macro.

Record the Keystrokes

Excel macros record the keystrokes and other functions you perform. When you stop recording the macro, it produces a simple “program” you can run over and over again.

To shift our address, we will start in cell A1,1 – where our first address is located. We are now going to add four new rows below the name to make space for the address details. So click cell A1,1 move down one cell to A1,2 and then hold down the shift key and use the down arrow and select four rows:

Now select the home tab and click “Insert” and then select “Sheet Rows” from the Insert menu. This will insert four rows below our address:

Now we will use the arrows to move to the first part of the address at cell C1. So click the up arrow to get to cell A1 and the right arrow to move to Cell C1. We will now use CTRL X to cut the information from C1 and then CTRL V to paste the information into Cell A2. Do the same for the information in Cell D1 and E1, using the arrow to move to each cell, CTRL X to cut and CTRL V to paste the information:

When you have copied the information to the new cells, use the mouse arrow to move to the cell that contains the next name and then click stop recording to stop recording your keystrokes.

Run Your Macro

That is all there is to it. Now all you need to do is click Macros, select your macro and click run or you can press SHIFT+”a” – because that was the shortcut we set for the macro. The information will automatically be moved based on the macro. Your new worksheet will look like this:

Use Macros to Cut Down on Repetition

Macros are a quick and easy way to cut down on repetition. For a comprehensive course on Excel 2013, enroll in the Microsoft Excel 2013 Beginners/Intermediate Training course now and go from Excel beginner to Excel master. This course offers over 58 lessons and 14 hours of video lessons to help you learn the basics of data entry, cell formatting and working with multiple worksheets. The course offers handy tips and tricks for mouse and touchscreen users. Once you have mastered the basics, the course will teach you about graphs and charts and it will teach you to work with templates. It includes lessons on SkyDrive and sharing your workbooks with others. The course also includes advanced lessons to help you take your Excel skills to the next level.