The last macro we recorded in the previous section had some drawbacks:

• It won’t go lower than you went when recording.
• You have to put your data in column A. It always puts the results in column B!

Ideally, we want the macro to adapt to the data we give it, instead of us having to adapt to the macro. Before we get there, let’s look at a simpler example.

Note the Use Relative References button on the Developer ribbon (also on the View ribbon, Macros pull-down menu). If it is off (not “lit up”) when you record, selection of cells will be recorded using absolute references and will always run in those same cells. If it is turned on (“pressed in”), cell references will be recorded relative to the cell where you started recording. If you later run the macro starting in a different cell, the macro will use cells relative to the new starting point.

We’ll start recording Absolute. Make sure that Use Relative References is off.

We will record a macro similar to our second one in the previous Section. This one will bold and underline a single heading cell in row 1, and adjust column width, then move one cell to the right, ready to run again.

We will use the same data, for example:

	A	B	C	D	E
1	Date	Division	Invoice	Amount	Destination
2	06/03/2018	East	1824	36,000	Cape Town
3	08/03/2018	East	1825	198,200	Johannesburg
4	09/03/2018	West	2782	40,000	East London
5	11/03/2018	West	2783	80,000	Cape Town

You can copy the data from the table above. Select (drag over) the data only (not the row and column headers shown), copy (Ctrl+C), start a new Excel file (Ctrl+N) and Paste > Text Only (see video).

Save the file as Headings1.xlsm to a suitable folder (Note: change the type to Macro-Enabled, ready to store our macros).

We will now record the macro. Do only the following, and do not forget to stop recording at the end:

1. Before recording, it is important that the cell selector is in the right cell to start off. This macro will operate on that specific cell. Click on Cell A1.
2. Make sure that the button Use Relative References is off (not “ lit up ”) (click it to turn it off, if it is on)
3. Use whichever of the three methods you prefer, to Record Macro.
4. Fill it in the Record Macro dialog box as follows:
   Macro Name: HeadingAbs
   Shortcut key: Ctrl+Shift+B (press only Shift+B – "Ctrl" is already there)
   Store macro in: This Workbook
   Description: Absolute Headings Macro
5. Click OK.
6. Record the following actions:
7. Bold (Ctrl+B or, on the Home ribbon, click the Bold B)
8. Underline (Ctrl+U or, on the Home ribbon, click the Underlined U)
9. Resize column A to fit (Double-click the divider between columns selectors A and B).
10. We want the cell selector in the next cell to the right, ready to run again, so press the Right Arrow button on the keyboard:
11. Stop recording.
12. Save the file.

Now test the macro: Press the shortcut key combination Ctrl+Shift+B

What happened?

You should have got the Bold and Underline, but not the column width adjustment, nor the move one cell right. Let’s manually move one cell right, and try it again.

What happened?

In fact, we find that the macro always finishes in cell B1, as it did when we recorded it.