Excel Macro To Save Sheets As PDF - Contextures

Home > Macros > Basics > PDF

In Excel 2010 and later, you can export a sheet, or a group of sheets, as a single PDF file. See how to manually export an Excel worksheet to PDF format, with the settings you need. Or, use the sample Excel VBA macros that automate the PDF conversion steps for you.

Export Sheet as PDF - Manual Steps

To manually export the active worksheet as a PDF file, follow these steps:

• First, click the File tab at the top left of the Excel window
• In the list at the left, click Export
• At the right, under the Export heading, click Create PDF / XPS Document
• Next, click the Create PDF / XPS button

Publish as PDF or XPS

The Publish as PDF or XPS window opens, where you can enter the details for the PDF file.

• At the top of the window, the Excel file's folder is showing. and there is a folder directory at the left side. If you want to store the PDF file in a different location, use the folder directory to find and open that location.
• In the lower section of the window, you can change the file name, if you prefer a different name
• In the Save as type box, the PDF (*.pdf) format is selected by default.
  • Note: The other file type in the drop down list is XPS, which is a similar format, developed by Microsoft.

PDF Settings & Options

• Below the file format drop down, select your preferences for these two settings:
  • Open file after publishing -- check box to turn on of off
  • Optimize for -- Standard or Minimum size
• Next, click the Options button
• In the Options dialog box, select your preferences in each section.
  • Page Range: For worksheets that print on multiple pages, choose which pages to print, or print all the pages on the worksheet
  • Publish What: Choose whether to publish the entire workbook, active sheet only, or the selected cells.
    • If the active cell is in a named Excel table, you can choose to publish that table only
  • Information: Choose if you want to include:
    • Document properties (Title, Author, Tags) from your Excel file .
    • Document structure tags for accessibilty such as Alt text (link to Microsoft site)
  • PDF Options - Check the box if you need to make the file PDF/A compliant (Wikipedia link)
• Next, click the OK button, to close the Options dialog box
• Finally, click the Publish button, to create the PDF file.

Export to PDF - 3 Macros

There are 3 Export to PDF macros in the sections below, and the next section shows the steps for using any of these pdf converter macros, in your own Excel file.

1. Macro 1 - Export Sheet as PDF
• PDF file uses Excel file's name, with a date and time stamp added.
• PDF file is stored in same folder as Excel file (if saved), or in Excel's default save location.
• Automatically overwrites existing file with same name, if any
2. -- Macro 2 - No Prompt
   • PDF file name from worksheet cell
   • PDF file is stored in same folder as Excel file (if saved), or in Excel's default save location.
3. -- Macro 3 - File Check
   • PDF file uses the Excel file's name, with a date and time stamp added.
   • PDF file is stored in same folder as Excel file (if saved), or in Excel's default save location.
   • Checks for existing file with same name. If found, prompts to overwrite or choose different name

Page Layout and Print Settings

When you create PDF files, manually or with a macro, the PDF document will use the page layout settings, and print settings, from the sheets that you convert to PDF.

• So, before you use these macros, be sure to set up any header, footer, page scaling, gridlines, print titles, and other settings that you want in the PDF documents.
• You should also check the page orientation (portrait or landscape), margins, page breaks, and print area settings.
• Use the Print Preview and Print command on the Quick Access Toolbar, or go to the File tab, and click Print (at the left side), to do a final check, before you create a batch of PDF files.

See the page setup tips and setting information on the Excel Printing Tips and Fixes page.

How to Use the Macros

To use the Export to PDF macros in your own Excel spreadsheets, follow the steps below.

1) Copy Code to Excel File

First, copy a macro's code from this page, and paste it into a regular code module in your Excel workbook.

• Tip: To use these macros in multiple Excel workbooks, save the code in a workbook that is always open, such as the Personal Workbook.

This short video shows the steps, and there are written steps on the Copy Macro Code page.

2) Modify Macro Code (Optional)

This step is optional - you can run the code as it is, without making any changes. However, you might want to change some of the PDF export settings, to get exactly what you need in the exported file.

For notes on how to modify the code, go to the Modify Export to PDF Macros section.

3) Run the Macro

When you're ready to run a macro, follow these steps:

• Select the worksheet(s) that you want to export as PDF.
• Next, on the Excel Ribbon, click the View tab
• At the right end of the tab, click Macros
• In the list of macros, select the PDF Export macro that you want to run
• Click the Run button
• The macro runs, and exports the sheet(s) to a PDF file.
• The macro shows a confirmation message, with details on the exported file's location

Warning: If you have a two or more sheets selected, remember to ungroup them, before doing any more work in your Excel file.

Macro 1 - Export Sheet as PDF File

This macro code exports the active sheet (or selected sheets) in PDF format.

• PDF file uses Excel file's name, with a date and time stamp added.
• PDF file is stored in same folder as Excel file (if saved), or in Excel's default save location.

Export As PDF Macro Code

See the next section, for details on how this pdf conversion macro works.

How The Macro Works

Before you run the macro in your spreadsheet, select the sheet(s) that you want to export to the PDF file.

Active Sheet and Workbook

When the macro starts, it sets variables for the active sheet, and the active workbook.

Those will be used to set the default file name and folder.

Date/Time Stamp

A time stamp will be added to the default name, in the format yyyymmdd_hhmm.

In the format string shown below, a backslash is entered before the underscore, to indicate it is a literal character. Otherwise, Excel would interpret the underscore as the spacing character that is used in Excel number formatting.

File Path

Next, the macro gets the default path for saving the PDF document. If the active workbook has been saved, its path is used. If the active workbook has not been saved, Excel's default save folder is used.

Sheet Name

The name of the active sheet is cleaned up -- spaces are removed, and periods are replaced with underscores.

File Path

The file path, revised sheet name, and the ".pdf" extension are combined.

Folder Location

The Save As dialog box opens, with the current folder selected, or the default save folder. The folder is filtered, to show only the PDF files that it contains.

At the top of the Save As window, the customized title is shown, "Select Folder and FileName to save"

Default File Name

The default file name is filled in, and you can overwrite it, to save the file with a different name. You can also select another folder --just browse to a different location.

Save Button

Then, click the Save button, or click Cancel, if you change your mind.

• If you click Cancel, the value of myFile is "False", and nothing more happens -- the macro ends.
• If you click Save, the PDF file is created.

Confirmation Message

Then, if the file was created, the macro shows a confirmation message with the file path and name.

Click the OK button to close the message box.

Macro 2 - No Prompt

The previous macro creates a default name with a time stamp, based on the active sheet name. It prompts you to select a folder for the saved PDF file, and you can change the default name, if you prefer something different.

In the macro below, the default name is based on the values in cells A1, A2 and A3 on the active sheet.

The PDF file is automatically saved in the current folder

• You are not prompted to choose a folder, and cannot change the default name.

Macro 3 - No Prompt - File Check

In the macro below, the default name is based on the values in cells A1, A2 and A3 on the active sheet. The PDF file is automatically saved in the current folder, with no prompts.

However, if a file with that name already exists in the current folder, a message asks if you want to overwrite the file. Click Yes or No in the message box.

• Yes - the new file overwrites the old file
• No - you are prompted to choose a folder, and/or enter a different file name.

NOTE: Be sure to copy the bFileExists Function too, below the main macro

Modify Export to PDF Macros

In the macros on this page, the code has settings for PDF files that are created from the Microsoft Excel spreadsheets. You might prefer different settings.

The best way to see how to change the code, so that it uses your preferences, is to record an Excel macro, while you manually export an Excel sheet in PDF format. To do that, follow the steps below.

Record a PDF Settings Macro

First, follow these steps to start recording a macro:

• At the bottom left of the Excel window, in the Status Bar, click the Start Recording button.
• In the Record Macro dialog box, type a one-word macro name, such as MyPDFOptions
• In the Store Macro In drop down, choose This Workbook
• Leave the Description box empty, or type a short note about the macro
• Click the OK button, to begin recording your steps.

Export as PDF

Next, while the recorder is running, export the active sheet to PDF, and choose your preferred settings.

• Note: There are step-by-step instructions in the Export to PDF - Manual Steps section.

After you click the Publish button, go to the Excel Status bar, and click the Stop Recording button.

Check Recorded Macro Code

To see the PDF code that was recorded, follow these steps:

• On the Excel Ribbon, click the View tab
• At the far right, click the Macros button
• In the Macro dialog box, from the Macros In drop-down list, select This Workbook
• In the list of macros, click on the macro name that you entered, e.g. MyPDFOptions
• Click the Edit button, to go to the recorded macro.

In the recorded macro code, you can see the export settings and options that you selected.

• In the screen shot below, I put blue boxes beside the settings from the Publish as PDF or XPS window:
  • Quality is either Standard or Minimum Size -- Standard was my selection
  • For the Open file after publishing setting, I removed the check mark.
• The red circles are beside the options I selected in the PDF Options dialog box.

Modify the Sample Code

In the Macros on this page, you can replace the sample code with your preferred settings. Just copy and paste from your recorded macro code.

• TIP: To open the VBE, use the keyboard shortcut - Alt + F11

For example, for the Quality setting, you could change:

• this setting, for Standard size: xlQualityStandard
• to this setting, for Minimum size: xlQualityMinimum

Error - Could not create PDF

If you run these macros in Excel for Office 365, you might see an error:

• Could not create PDF...

Or, if you try to manually export a PDF file in Excel for Office 365, you could see this error:

• Document not saved. The document may be open, or an error may have been encountered when saving

In one of the Excel forums on the Microsoft website, someone posted the following solution to the problem.

• As always, make a backup of the Registry, before making any changes to it, and try this at your own risk!

'====================

Quoted from Microsoft Excel Forum:

After hours of studying Process Monitor reports on two different computers and comparing them, I discovered that Microsoft Office apps look for "sRGB Color Space Profile.icm" in the wrong place and don't find it. By deleting the following Registry values, the problem goes away:

• Path: HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\ICM\RegisteredProfiles
  • Value name: sRGB
• Path: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ICM\RegisteredProfiles
  • Value name: sRGB

For the record, these values are all okay. There is nothing wrong with them. (The Color Management applet in the Windows Control Panel creates them.) The blame is entirely on Microsoft Office, i.e. this is a bug.

'====================

Get the Sample File

To see how the macro works, you can download the Export Excel Sheet as PDF sample file. The zipped file is in xlsm format, and contains macros. Be sure to enable macros, if you want to run the macro.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Subscribe.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Tutorials

Email from Excel with PDF

Copy Macro Code to a workbook

Excel VBA Edit Your Recorded Macro

Excel VBA Getting Started