We used to print charts and other analysis reports to share with others. Today, we can bypass the printing route by sharing reports electronically. However, sending the entire Microsoft Excel workbook file is usually inappropriate; you’ll seldom want to share the source data along with reports. One way to share only the report is to save that sheet to a PDF file and then share it electronically—no fuss, no muss, no waiting for a printout to route through an archaic in-house mail system. In this article, I’ll show you how easy it is to save only the active sheet to a PDF file by using the interface and by running a macro.
SEE: 69 Excel tips every user should master (TechRepublic)
I’m using Microsoft 365 on a Windows 10 64-bit system. You can work with your own data or download the demonstration .xlsx file. This article isn’t appropriate for the browser edition nor the earlier .xls format.
The report vs. the spreadsheet
If you’re like many users, you separate data from reports. That way, you can quickly print a report without also sharing the source data. It’s quick and clean. Figure A shows such a reporting sheet. If you send the entire workbook electronically, others have access to the source data, which might be confidential or proprietary. Besides, including the data in a report is distracting.
As mentioned, you can print the report and route it appropriately, but instead, let’s save it to a PDF file that you can share by email, on OneDrive, or attaching via a chat window or Team meeting. We won’t spend time on creating the report sheet, so we can concentrate on saving the report sheet. (If you’re using the demonstration file, you can view the source data in the Stratification tab.)
How to manual save as a PDF in Excel
Saving the report sheet to a PDF file is the easy part. From the report sheet, click the File tab. In the left pane, choose Save As or Save a Copy (OneDrive). In the resulting dialog, choose PDF (*.pdf) from the Save dropdown (Figure B), and then click Save. You can also change the folder or filename when necessary. You can see the resulting PDF file in Figure C. If the workbook has more sheets (and most will), this process saves only the active sheet.
How to use a macro to save as a PDF in Excel
You probably didn’t need me to show you how to save a sheet to a PDF file but knowing that the task saves only the current sheet might be new to you. If you do this often, you might benefit from a macro that saves the current sheet to a PDF file—it only takes one line of code. To add the code, do the following:
- Press Alt+F11 to open the Visual Basic Editor (VBE).
- Find Sheet2 (Stratification) in the Project Explorer to the left (update the sheet name if you’re working with your own file). Double-click to open the sheet’s module.
- Enter the code in Listing A into the sheet module (Figure D). Don’t copy from this web page. You can import the demonstration .bas file or enter the code manually.
‘Save Report sheet (Sheet2) as pdf file.
Next, return to Excel and save the file as a macro-enabled file. To run the macro, click the Developer tab, click Macros in the Code group, and then choose the SaveReportPDF sub procedure and click Run.
If the Developer tab isn’t visible, you can find instructions in the article How to add Office macros to the QAT toolbar for quick access. (See the first paragraph following Figure J.)
The short procedure does the bear minimum and makes a lot of decisions for you. As is, this procedure saves the active sheet (Stratification) to a PDF file using the name of the Excel file to name the PDF file, and saves that file in the same folder. It takes care of a lot for you, but you might need to adjust things a bit. The syntax for the full command is
ws.ExportAsFixedFormat (Type, FileName, Quality, IncludeDocProperties, IgnorePrintAreas, From, To, OpenAfterPublish, FixedFormatExtClassPtr)
where only Type is required. In our case, we used the constant xlTypePDF and left the OpenAfterPublish as its default, true. The name of the demonstration workbook is ExcelPDF_demo.xls, so the macro saves the Stratification sheet to a PDF file named ExcelPDF_demo.pdf in the same folder as the workbook file, and then opens the newly saved PDF file. You can explore the other parameters further if you think you might need them.
You might want to control the name and the folder where the macro saves the file. If so, you’ll probably want to add some error-handling code. In addition, the ws expression, which must reference a worksheet object isn’t necessary, and when not explicitly referenced, defaults to the active sheet. That means, you must go to the Stratification sheet to execute the macro. You could easily specify the worksheet using a Dim ws As Worksheet line and define it accordingly.
This article shows you how to save the active sheet as a PDF and I’ve provided a simple macro that does the same thing. You can, if needed, add to that macro to specify the filename and the default folder.