Excel - Macros

Tags Excel Macros

Background Information

Macros automate repetitive tasks by recording and re-playing a series of actions recorded in an Excel workbook. The following Job Aide provides the steps needed to record and replay a Macro. A common scenario for the creation of a Macro is to apply formatting to an Excel worksheet that was created as an output (report) by another program.

Macro Setup

  1. Open an Excel file that requires a repetitive task.
    • Example: Apply identical formatting and/or functions to rows, columns, or cells in workbooks that are outputs of an Enterprise Application (report from SWS, CMP, etc.).
  2. Select cells that require formatting. If the range of cells does not change from file to file, select the exact range. If the Macro should be applied to a row or column as a whole, select the row header or column header.
    • Review an example of this within the pdf attachment.

Record Macro

  1. Following Setup steps, select Record Macro. View Tab > Macros section > Macros drop-down > Record Macro.
  2. In the Record Macro dialog box, enter the following:
    1. Macro name (do not include spaces in the name)
    2. Add Shortcut Key to copy Macro.
      1. Note that existing shortcut keys will not save. Only unassigned shortcut letters will be saved as a shortcut key.
    3. Store macro in:
      1. This Workbook – Macro will save in this file only.
      2. Personal Workbook – Macro will be available in all workbooks opened from your user account.
    4. Description – add if needed
    5. Click OK to save Macro information and start recording.
  3. Using the example in the Setup section, the following actions would be recorded:
    1. Right-click Column E.
    2. Select Delete from the menu.
    3. Right-click Column G.
    4. Select Delete from the menu.
  4. When all actions are complete, click on the Macros drop-down, select Stop Recording.

Note: The Stop Recording button can also be found in the lower left corner of the Excel program. Look for the Blue Square.

View and Run Available Macros

  1. From the Macros drop-down, select View Macros.
  2. All available Macros are listed. Select the appropriate Macro and click Run.
  3. Save the file as a Macro-Enabled Workbook to keep the Macros available in the file.

More Macro Information

Advanced Macros to be used with cells including Formulas and Relative References are also available. For more information, log into Center for Learning, and search Advanced Macros. Select the course titled Automating Excel 2010 Tasks Using Macros.

Please note: Macro steps have not changed from Excel 2010 > Excel 2016 > Excel 365. All steps in the course video will apply to Excel 2016 and above workbooks.

 

To review images or save/download/print a copy of this Job Aide, please select the pdf attachment.

Print Article