There are two methods of recording a macro, and each has advantages depending on how you use them: the macro with the absolute reference and the macro with the relative reference.
The macros with the relative reference are recorded with actions relative to the initial selected cell. For example, if you record a macro with the relative reference option on where the action is to move the cursor from cell B2 to B3, and later play the macro starting from a different cell (e.g., D3), the macro will mimic the relative movement from that new starting position. Hence, when you play the macro from D3, the cursor will move to D4 as it emulates the relative move from the new initial cell.
The macro with the relative reference is beneficial when you need flexibility in applying the macro across different locations or scenarios. For instance, if the same set of operations needs to be repeated in various places, a relative reference macro adapts to the specific location where it's applied.
If the macro is recorded without the "Use Relative Reference" option, the recorded action will always refer back to the absolute reference of the initially selected cell. So, when played from D3, it would execute the same action as when it was initially recorded, moving the cursor back to the same relative position from the starting cell, in this case, B3.
Understanding when to use each method can significantly enhance the effectiveness and efficiency of macros, depending on whether you need fixed, absolute cell references or a more adaptable, relative approach for repetitive tasks.
We already recorded a macro without the relative reference, and the following is one example of recording a macro with the relative references on.
Part I: Record a macro with relative references on
Step 1: In the "Developer" Tab from the Ribbon, check the button of "Use Relative References" and click the "Record Macro";
Step 2: In the new window, type the Macro name, and fill in the three other sections (optional).
- Please note that the first character of the macro name must be a letter. You can use the underscore, but the space is not allowed in the macro name.
- The shortcut key section and the description section are optional.
Step 3: Click "OK" to save the macro. In the worksheet, highlight cells B2:B11. Click "Stop Recording" in the "Developer" tab after you complete.
Part II: Play a macro with the relative references
Step 1: Move your cursor to any cell and click the "Macros" command;
Step 2: In the new window, select the macro name from the list and click "Run";
Step 3: A new range will be highlighted. In this example, our cursor starts the cell F3.
