Wednesday, October 7, 2015

Macro Muscle

Today our topic is the often mystifying subject of Excel Macros.  Macros are typically known as tools that are only accessible to the special skillset of VBA programmers.  On one level, however, that is not entirely true.

If, for instance, you have workbooks which you need to continually update with new data and continually repeat certain tasks, a Recorded Macro can be your ticket to lessening your repetitious work and simplifying your routine chores.

A Recorded Macro can “remember” your mouse clicks and keystrokes while you work, and then allows you to play them back in future revisions and repetitions of your workbook. You can save your recordings, and when you run the macro, it will play the commands in the same order that you recorded them. It gives you true Macro Muscle at your command!

For example, let’s say you track the performance of the Sales Reps in your company. Something like this can be a boring, repetitive task, as each week a report may be needed for management.  It can be very dull, but it needn’t be so. Here's how to Record a Macro to address these updates:

1.   First of all, in your report workbook, click the start of the cells you are going to update.
2.   Point to the Developer tab, and then click Record Macro.
3.  In the Record Macro dialog box, enter a Name that applies to your operation. For ease of operation later on, assign a custom Shortcut Key (this will separate you from other mere mortal Excel users…).
4.   Now perform the calculations, formatting, moving, etc that applies to the repetitive and monotonous update.
5.   Finish recording the macro by clicking the Stop Recording button.

To Run your Macro, simply go to the update start cell and enter (or click) your Custom Shortcut Key.

Presto! Instant update! Your newly created Macro has just done all of the work that may have taken you several minutes or an hour to complete. Give it a try, you may find you have more Muscle than you ever knew!

No comments: