Microsoft Excel: The ONE Automation Macro You Need to Use

If you sit in front of a computer screen all day creating reports in Microsoft Excel, you know the power of this software package. But if you aren’t using macros yet, you don’t know the half of it. Macros automate common and repetitive keystrokes that you use in Excel to create and edit spreadsheets.”

Macros in Excel are intimidating to a lot of users. The beauty of macros, though, is they allow automation that dramatically simplifies our daily use of Excel. Additionally, with the ability to use the Record capabilities in Excel and search on Google, you can literally be up and running with little to basic knowledge of VBA (Visual basic for applications).

So, what one macro do I recommend? A “print to PDF” macro. Here’s what it does and how you can set it up.

  • Once print parameters have been set, this macro prints the current worksheet to PDF
  • The macro names the file with the Sheet Name + a date and time stamp based on when it was printed
  • It saves the PDF to the CURRENT folder where the Excel file is located

Here’s how you set it up.

Activate the Developer Tab in Excel (this is not shown by default in Excel — you must turn it on)

  • Click on the File menu and then select Options from the drop down menu

MS excel File menu screenshot.

Select “Customize Ribbon” and click on the “Developer” box under Main Tabs.

Excel options - customize ribbon - developer
  • The “Developer” tab will now be displayed on your Excel Ribbon toolbar.
developer option on ribbon

To implement the “Print To PDF” macro, I will use a sample workbook (which you can download here if you want to refer to it).

preview of excel sheet

The VBA macro code may be confusing, but you don’t need to understand it to implement it (I don’t). The VBA Code can be found here — you will need to copy and paste this into your Excel workbook (and it can be any workbook). Here’s what it looks like:

screenshot of The VBA Code

The steps I walk through apply to any Excel file you want to place the PDF print macro in. I am using the sample file above to highlight the steps. So here we go:

  • Save the Excel workbook as a .xlsm format file. Macros must be stored in a macro-enabled workbook format.
  • Click on the Developer tab and select Visual Basic
screenshot of visual basic in ms-excel
  • In the Visual Basic editor, select the “VBAProject” for your file, right-click the mouse → Insert → Module.
screen shot of VBA project - insert- module

A new folder under the VBAProject for the workbook is created called “Modules” and it now contains a sub-item called “Module1

screenshot of module 1 window.

Paste the VBA code in the module as indicated above and CLOSE the VBA Editor

screenshot of code pasted in module 1

You will be returned to your workbook and the macro is now available to be used in your workbook. Access the Macro list by clicking on Developer → Macros, as follows:

screenshot of macros button appeared on developer ribbon.

Verify the macro name and close the Macro dialog box

screenshot of verifying the name
  • Next we are going to create a shape and assign the macro to the shape we create. Then, to invoke the macro, simply click the shape! (MAKE SURE TO SAVE YOUR FILE!)
screenshot of shape folder

screenshot of selecting the shape for button
screenshot of modifying the shape properties to turn off printing the object.

Assign the macro to the Shape that you just created; Right-click the shape to select it and a shortcut menu should appear. Select Assign Macro.

screenshot of assigning the macro
  • Once selected, click “OK” to exit. Click off of the object to DESELECT it.
screenshot of final window
  • Test the macro! A Save dialog box will appear — note name is structured as [tab name +yyyymmdd + time.pdf]. Select Save and you are done!
screenshot of testing the
screenshot of file pdf

This article was originally written by Don Tomoff

About author:

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

CONNECT WITH DON! LinkedInFlipboardTwitterSnapchat