How to create Macros with Absolute and Relative References

An Excel Macro is a set of instructions that can be triggered by a keyboard shortcut, toolbar button or an icon in a spreadsheet. Macros are used to eliminate the need to repeat the steps of common tasks over and over.
Tasks such as adding or removing rows and columns, protecting or unprotecting worksheets, selecting a range of cells, or adding the current date to a spreadsheet. In Excel, macros are written in Visual Basic for Applications (VBA).

Recording Macros. When you record a macro, Excel stores information about each step you take as you perform a series of commands. You then run the macro to repeat, or "play back," the commands.

Here are the basic steps on how to create macros

1. In excel default tab noticed that there’s no Developer Tab appear in ribbon, in order to do that.

2. In Office Button, click office button and just below click Excel Options.

3. In an Excel Options, click on the Popular. If you notice we have 3 top options for working with windows.

4. Check the box show developer tab in the ribbon.

5. Now you have already a developer tab in your ribbon.

Let us recall what is Relative and Absolute Reference:

Relative Cell References that will change in relation to the new location of the formula. Relative references identify cells based on their relationship to the cell containing the reference.

Absolute Cell References that remain the same when a formula is copied to a new location. No matter where the formula or the values in the original cell are moved, the formula will continue to refer to the same cell.

These tutorials show on how to create simple Marcos with absolute and relative references.

Absolute Reference:

1.   Create column name, First name in A1 and Last name in B1.

2.   Now, we want to record our macros. In your tab ribbon, click the developer tab.

3.   Before we start typing the name. First, click Record Macros.

4.   When you noticed, another dialog windows appear. Name you macros as MacAbsoluteRecord. If you want for short cut key, it’s your choice. Then click OK.

5.   It’s time to add the data in your first name and last name. So, we have 3 input rows.

6.   Now, Stop Recording.

7.   To run macros. Click on sheet 2, Create column name, First name in A1 and Last name B1.

8.   In your developer tab, click macros and locate the name of your macros you’ve already created “MacAbsoluteRecord”.

9.   Click run.

10. If you want to run your macros anywhere in your sheets, the same column and rows that the record you’ve created will display, because absolute reference does not change whenever the formulas created in your worksheets.

11. You may able to run you macros as shortcut command based on your short cut key. Just like Ctrl + m.

Relative Reference:

1.   Before we start typing our data, we want to records first as relative reference. In order to do that, in your developer tab under Record Macros, click Use Relative References.

2.   When you notice, Use Relative Reference is now active and then click Record Macros. Name it, "MacRelativeRecord".

3.   Now, it time to record. In your column name, type Name and Gender. So we have 3 input rows in Name and Gender

4.   Then stop Recording.

5.   In your developer tab, click macros and locate the name of your macros you’ve already created “MacRelativeRecord”.

6.   Click anywhere in your sheets to run macros and select the name of your macros.

7.   Noticed when you run the relative reference, the first destination of your macros record is based on the intersection of column and row where the cell is active, because relative references change whenever the cell is active.

Here are the examples Screenshot

Absolute References:

            Relative Reference