This Tech Tip was provided by Christina Butler, Research Analyst, Tufts University.
Our office produces many department-level and individual-level reports. These reports are often created in an Excel workbook containing worksheets named after each department or individual. For example, we create advising reports for all of our academic advisors in an Excel workbook that contains a worksheet for each individual advisor’s report. Because we give each advisor his/her own report as a PDF, we must save each workbook as a PDF file. The process can actually be completed quickly through the use of VBA code in Excel, as this Tech Tip explains. I have included a link to the VBA code along with some directions, but for more documentation on using this code, please visit the Microsoft website.
1. Within your Excel workbook, create a worksheet titled “WorksheetNames.” In column A of the “WorksheetNames” worksheet, list all the names of the worksheets for which you plan to create a PDF. It is important that the names of the worksheets be reproduced exactly.
2. Open the Visual Basic Editor in Excel (Developer - Visual Basic).
a. Because the Developer tab does not appear in Excel 2010 by default, it may need to be enabled.
To enable the Developer tab:
On the File tab, choose Options to open the Excel Options dialog box.
Click Customize Ribbon on the left side of the dialog box.
Under Choose commands from the left side of the dialog box, select Popular Commands.
Under Customize the ribbon on the right side of the dialog box, select Main tabs, and then select the Developer check box.
b. Additional directions on using VBA in Excel can be accessed on the Microsoft website.
c. Once the VBA Editor is open, you may need to change the view so that you are in “Code View.” To do this, go to View - Code.
3. Paste the code located in the Word document (and shown below as reference) into the Visual Basic Editor. The variable “numSheets” will need to be replaced with the number of worksheets that will be saved as a PDF file. (In the example above, it would be 4). Also, the filename should be modified to reflect the appropriate file path. Note that lines starting with an apostrophe are macro comments that will not be executed by Excel VBA. A macro comment is a piece of text that provides information about the macro.
4. Run the code and verify that the PDF files have been saved in the appropriate destination folder. You may encounter an error if the worksheets you are trying to save as PDF files are empty.