Your First Macro: Frequently-Repeated Text
If you’re typing the same data several times a day, you need a macro. Even if that information is short, a macro does it in seconds, standardizes it, and eliminates mistyping. This macro inserts your company details and today’s date and time at the top of the current worksheet.
- Create a new, blank, worksheet and type something in row 1.You can also use an existing one, but make sure you have saved it first. Do not save it again until you are happy with the results!
- Can you see the status bar at the bottom of the Excel window? It shows “Ready” on the left, followed by an icon of a spreadsheet with red dot.
- That Icon is what we want.Click it (note that the button changes to a square Stop Recording).
Note: In Excel 2013 and later, you might not see that icon [Microsoft has a habit of hiding useful features]. In that case, on the View ribbon, click the drop-down arrow under the Macros button, and select Record Macro…
- The Record Macro dialog box will pop up. Fill it in as follows:
Macro Name: CompanyTitle [Note: spaces are not allowed. Use underscore “_” instead of space]
Shortcut key: Ctrl+Shift+T (press only Shift+T – "Ctrl" is already there)
Store macro in: Personal Macro Workbook
Description: Add Company details and today’s date at the top of the current worksheet.
Notes: We name the macro using a concise name starting with a letter.The macro name can be as short as one character and there is no maximum. However the name can only be one word: Underscores and numbers can be used. The description should give a meaningful explanation of what the macro does, as well as the date it was created
- Click OK.
- Select Rows 1-3 by clicking on and then dragging over row indicators 1-3 on the left of the spreadsheet.
- Hold in the Ctrl key and press + to insert selected rows.
- In cell A1, enter the company information, e.g. Charlatan Corporation Inc., Johannesburg.
- Format cell A1 as a heading, for example Arial 18 pt bold.
- In cell A2, Type =now( and press Enter.
- Format cell A2 bold.
- If you don’t like the date format you have, Press Ctrl+1, select the Number tab, and change it.
- Copy cell A2 and Paste Values.This converts the formula to the current date and time.Otherwise it will change every time the spreadsheet recalculates.
- You’re done!But wait, you must stop the macro, or it will keep recording stuff you don’t want:
- Stop recording the macro: On the Status Bar, click the Stop Recording button:
- Close the file.Save it if you want to keep your changes.
Close Excel. It will ask if you want to save the Personal Macro Workbook. Choose Save, because that is where your macro is stored.
To test the macro, run Excel again.
- Create a new, blank, worksheet and type something in row 1. You can also use an existing one, but make sure you have saved it first.
- We used the Shortcut key: Ctrl+Shift+T. Press that key combination now.
- Did it work?