Chapter 14. Macro-Charged Reporting

In This Chapter

  • Introducing macros

  • Recording macros

  • Setting up trusted locations for your macros

  • Adding macros to your dashboards and reports

A macro is essentially a set of instructions or code that you create to tell Excel to execute any number of actions. In Excel, macros can be written or recorded. The key word here is recorded.

Recording a macro is like programming a phone number into your cell phone. You first manually dial and save a number. Then when you want, you can redial those numbers with the touch of a button. Just as on a cell phone, you can record your actions in Excel while you perform them. While you record, Excel gets busy in the background, translating your keystrokes and mouse clicks to written code (also known as Visual Basic for Applications (VBA)). After a macro is recorded, you can play back those actions anytime you want.

In this chapter, you'll explore macros and learn how you can use macros to automate your recurring processes to simplify your life.

Why Use a Macro?

The first step in using macros is admitting you have a problem. Actually, you may have several problems:

  • Problem 1: Repetitive tasks. As each new month rolls around, you have to make the donuts (that is, crank out those reports). You have to import that data. You have to update those pivot tables. You have to delete those columns, and so on. Wouldn't it be nice if you could fire up a macro and have those more redundant parts of your dashboard processes done automatically? ...

Get Excel® Dashboards & Reports now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.