How to Convert Individual Sheets in a Workbook Into Separate PDF Files

If you are using Microsoft Excel for work or business, you might find it useful to print the Excel sheets as PDF file for better reporting as the contents of the PDF files cannot be modified easily compared to a raw Excel file. Besides, most computers and mobile devices can read PDF files but cannot read Excel files as MS Excel is a proprietary software.

There are basically two (2) ways to produce a PDF file from MS Excel file as described in the succeeding texts.

Converting Excel file to PDF Through PDF Printer

This will not only work with MS Excel. Since it is a PDF printer, anything that you print will simply be converted to PDF instead of actually printing it on paper. Thus, this will work on almost all programs that will initiate printing; be it MS Word, MS PowerPoint, plotting software, graphic software, or anything else.

This however needs a software that will convert the print job to PDF. And among the widely used PDF printer is CutePDF or PDF Creator. With CutePDF, after installing it, you will see a virtual printer called CutePDF Writer. So when you print, just select the PDF printer and the output will be a PDF file.

When you want to convert anything into a PDF file, you just initiate the printing process and then select this PDF printer as your target printer and you shall be prompted to save the file as PDF.

Saving the Excel File as PDF

This feature is available only in the recent versions of MS Excel like Excel 2007 and higher. The steps are described below:

1. Open your Excel file 2007
2. Click on the MS Office Orb (circle with MS Office logo)
3. Point to Save As and then select PDF or XPS.

4. If you are using Excel 2010 or higher, click on File menu
5. Click on Save As
6. In the Save As dialog box, select PDF as the file type.
    

Converting Entire Excel Workbook as a Single PDF File

If you want to convert your entire Excel workbook as a single PDF file, it will be easy. The approach to solve this problem is NOT to use the Save As method because if you use the Save As method, only the active sheet will be converted to PDF. So we will be using the PDF printer selecting the entire workbook in the print dialog.

Here are the steps for MS Excel 2010:

1. Open the Excel file you want to print entirely
2. Click on File => Print or press Ctrl+P to bring up the Print dialog box.
3. For the printer, select your PDF printer. In my sample, it is the CutePDF Writer.
4. Under settings, the default value is “Print Active Sheets”. Click on it then select “Print Entire Workbook”.

5. Then click on the Print button.

Converting Individual Excel Sheets as Individual PDF Files

In this case, we cannot use the PDF printer and the Save As methods to convert the individual sheets as individual PDFs bearing the sheet names as their file names. This is done by using a macro.

Here are the steps to add the macro to your Excel file:

1. Open your Excel file
2. Press ALT+F11 to open the VBA window
3. In the VBA window, click on Insert then click on Module
4. In the module window, paste the code below:

Sub SaveWorksheetsAsPDFs()
    Dim sFile       As String
    Dim sPath       As String
    Dim wks         As Worksheet

    With ActiveWorkbook
        sPath = .Path & "\"
        For Each wks In .Worksheets
                sFile = wks.Name & ".pdf"
                wks.ExportAsFixedFormat Type:=xlTypePDF, _
                                        Filename:=sPath & sFile, _
                                        Quality:=xlQualityStandard, _
                                        IncludeDocProperties:=False, _
                                        IgnorePrintAreas:=False, _
                                        OpenAfterPublish:=False
        Next wks
    End With
End Sub

5. After pasting the code, click on the Save button
6. When prompted, choose to save the file as Macro-Enabled with the XLSM format. This will ensure that when you close and open the file, the added macro code will be saved with the file.
7. To run the macro, while in the main Excel window (not the VBA window) click on Developer tab then click Macros
8. Locate the macro named SaveWorksheetsAsPDFs and then click on RUN.
9. Then check your My Documents. PDF files named after the sheet names were created.

The sample Excel file with the macro can be downloaded below.

Google Drive Shared File