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.”
Source: http://www.ehow.com/info_8085042_advantages-excel-macros.html
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
- Select “Customize Ribbon” and click on the “Developer” box under Main Tabs.
- The “Developer” tab will now be displayed on your Excel Ribbon toolbar.
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).
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:
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
- In the Visual Basic editor, select the “VBAProject” for your file, right-click the mouse → Insert → Module.
- A new folder under the VBAProject for the workbook is created called “Modules” and it now contains a sub-item called “Module1”
- Paste the VBA code in the module as indicated above and CLOSE the VBA Editor
- 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:
- Verify the macro name and close the Macro dialog box
- 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!)
- 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.
- Once selected, click “OK” to exit. Click off of the object to DESELECT it.
- 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!
- The PDF can be viewed here. Notice that the buttons DO NOT appear on the printed document. An image is below:
About Don
“On a mission to challenge the status quo to a more productive and effective end…”
Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.
