How to save specific multiple worksheets to a pdf file programmatically

This article demonstrates macros that export worksheets to a PDF file.

1. Introduction

A macro is program or subroutine written in VBA (Visual Basic of Applications) that performs certain actions. Anyone with access to Microsoft Excel can create a macro. The "Developer" tab allows you to create edit and record macros, however, the tab is hidden by default.

Here is how to enable the "Developer" tab. Press with left mouse button on "File" located above the ribbon, a new pane appears. Press with mouse on the "Options" button to access Excel settings. Press with mouse on "Customize Ribbon" and then on the right side press with left mouse button on the checkbox next to the tab "Developer" to enable it. Press with left mouse button on "OK" button to apply changes. The "Developer" tab is now visible on the ribbon.

The leftmost button on the "Developer" tab is named "Visual Basic". This button opens the "Visual Basic Editor" which allows you to create macros, user defined functions and VBA Events. This article demonstrates how to automatically create PDF files based on given worksheets using a VBA macro.

• Macros are sequences of instructions or actions based on VBA, they are useful to automate repetitive tasks.
• User defined functions are custom functions that you create using VBA to extend Excel’s functionality. They allow you to create more advanced functions to meet specific needs, however, much similar to Excel's built-in functions like SUM, COUNT and IF.
• Excel Events are triggers that run VBA code in response to specific actions or events in Excel. These events can be when a user opens a workbook, starts a specific Excel feature or changes in the worksheet.

A PDF file (Portable Document Format) is a widely used file format developed by Adobe Systems in the early 1990s to make sharing and viewing documents across different devices and operating systems easier. PDFs are designed to preserve the formatting, layout, and content of documents regardless of the software, hardware, or operating system used to view them.

You can easily export an Excel worksheet to PDF manually. Go to "File" on the ribbon. Press with mouse on "Export" and then press with left mouse button on "Create PDF/XPS Document" button. A dialog box appears prompting you for a destination folder and a file name for the PDF file. Exporting all worksheets in a workbook to PDF programmatically can be useful for several reasons.

• Batch processing: Automating the export process saves time and effort, especially when dealing with large workbooks or multiple workbooks.
• Consistency: Ensures that all worksheets are exported in a consistent format. PDF readers can be geted for free, everyone can open and access the files.
• Sharing and collaboration: PDFs are widely supported and can be easily shared with others, making it a convenient format for collaboration and review.
• Archiving: Exporting worksheets to PDF creates a snapshot of the data at a particular point in time, making it useful for archiving and auditing purposes.
• Reporting: PDFs can be used to generate reports that can be easily distributed to stakeholders, such as management, customers, or regulatory bodies.
• Easy integration: PDFs can be easily integrated into other systems, such as document management systems, or used as input for other processes, such as data analysis or machine learning.
• Security: PDFs show only the selected worksheets, not the formulas and data behind the formulas.
• Design: By assigning a macro to a button the user can quickly export to PDF.

By exporting all worksheets in a workbook to PDF programmatically, you can automate this process and take advantage of these benefits.

2. Export all worksheets in workbook to PDF programmatically

The following macro saves or exports all worksheets in the active workbook to a single pdf file. Copy the code below and paste to a new module in your workbook using the VBE (Visual Basic Editor) which you can access by pressing on the "Visual Basic" button on the "Developer" tab or by pressing shortcut keys Alt + F11. This is explained in detail in section 3 below.

The VBA code contains comments that explain what each line does. Every comment starts with a ' which tells Excel that this is a comment. They are used to add explanatory notes to the code without affecting its execution. For example, programming language python uses the hashtag for a single line comment. It's highly advisable to include a brief description of what a macro does immediately after its name. This practice will prove invaluable in the long run, especially when revisiting the macro years later.

Each macro always starts with "Sub", the name of the macro and a starting and closing parentheses. The name must be unique in this workbook meaning no other macro can use the same name. You can create any name, however, I recommend a name that describes what the macro performs. The macro ends with "End Sub"

'Name macro
Sub ExportWbtoPdf()

'Select all worksheets in active workbook
For Each WS In ActiveWorkbook.Worksheets
    Worksheets(WS.Name).Select False
Next WS

'Ask for a directory to save the pdf file in
With Application.FileDialog(msoFileDialogFolderPicker)
    .Show
    myfolder = .SelectedItems(1) & "\"
End With

'Ask for a save file name for the pdf file
myfile = InputBox("Enter file name", "Save as..")

'Save all worksheets in workbook to pdf file
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    myfolder & myfile _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True

End Sub

The FOR EACH ... NEXT loop is used in VBA to iterate through all elements in a collection or array, in this case, worksheets in a workbook. It is particularly useful when you want to process each item in a group without needing to manage index counters manually.

The WITH ... END WITH statement simplifies working with a specific object by allowing you to run multiple actions on it without repeatedly referencing the object. This also makes the code shorter and easier to read.

The INPUTBOX function is used to display a dialog box that prompts the user for input. It returns the value entered by the user as a string.
Syntax: InputBox(prompt[, title][, default][, xpos][, ypos][, helpfile, context])

The ExportAsFixedFormat method is used to save an Excel worksheet, workbook, or chart as a PDF or XPS file.
Syntax: ActiveSheet.ExportAsFixedFormat(Type, Filename, Quality, IncludeDocProperties, IgnorePrintAreas, From, To, OpenAfterPublish)

Where to put the code?

2.1 How to start the macro?

After you have saved the above code to a code module you can start the macro by:

1. Go to the "Developer" tab on the ribbon.
2. Press with left mouse button on the "Macros" button, a dialog box appears.
3. Select macro name "ExportWbtoPdf()"
4. Press with left mouse button on the "Run" button.

2.2 How to use the macro?

1. The macro shows a input box or a dialog box after starting the macro.
2. Type the file name in the input box.
3. Press with left mouse button on the "OK" button.

The macro now saves each worksheet in the workbook to the PDF file name you specified.

Back to top

3. How to save specific multiple worksheets to a PDF file programmatically

This VBA macro allows you to specify which worksheets in you workbook to export to PDF files. In this context, programmatically means that the code is a VBA subroutine.

'Name macro
Sub SaveSelectedSheetsToPDF()

'Dimension variables and declare data types
Dim str As String, myfolder As String, myfile As String

'Get names from worksheets based on selection
str = "Do you want to save these sheets to a single pdf file?" & Chr(10)
For Each sht In ActiveWindow.SelectedSheets
str = str & sht.Name & Chr(10)
Next sht

'Display a Message box prompting if the macro should end or continue exporting
answer = MsgBox(str, vbYesNo, "Continue with save?")
If answer = vbNo Then Exit Sub

'Pick a destination folder for the PDF file
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
myfolder = .SelectedItems(1) & "\"
End With

'Ask for a PDF file name
myfile = InputBox("Enter filename", "Save as..")

'Save the pdf file based on folder and name
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
myfolder & myfile _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

'Quit macro
End Sub

The Dim (short for "Dimension") statement is used in VBA to declare variables. Declaring variables is a way to allocate memory and define the type of data a variable can hold. This may potentially save memory and make macros quicker to run and process.

The If ... Then statement is used to run a block of code based on a condition. This statement is fundamental in VBA programming. The condition is a logical expression that evaluates to True or False.

The FOR EACH ... NEXT loop is used in VBA to iterate through all elements in a collection or array, in this case, worksheets in a workbook. It is particularly useful when you want to process each item in a group without needing to manage index counters manually.

The WITH ... END WITH statement simplifies working with a specific object by allowing you to run multiple actions on it without repeatedly referencing the object. This also makes the code shorter and easier to read.

The INPUTBOX function is used to display a dialog box that prompts the user for input. It returns the value entered by the user as a string.
Syntax: InputBox(prompt[, title][, default][, xpos][, ypos][, helpfile, context])

The ExportAsFixedFormat method is used to save an Excel worksheet, workbook, or chart as a PDF or XPS file.
Syntax: ActiveSheet.ExportAsFixedFormat(Type, Filename, Quality, IncludeDocProperties, IgnorePrintAreas, From, To, OpenAfterPublish)

3.1 How to use macro SaveSelectedSheetsToPDF?

Make sure you select the worksheets you want to save as a single pdf file before you run the macro. To select multiple worksheets press and hold CTRL and then press with left mouse button on with the mouse on the worksheet tabs located at the bottom of your Excel screen.

This happens when you start the macro

1. Confirm selected sheets by press with left mouse button oning "Yes".
   
2. A dialog box appears, choose a save folder. Press with left mouse button on OK.
3. A new dialog box appears. Enter a file name.
   
4. Press with left mouse button on OK button.
5. A pdf file is created and opens automatically.

Back to top

4. Where do I put the code in my workbook?

1. Start the Visual Basic Editor (Alt+F11).
2. Press with left mouse button on "Insert" on the top menu, see image above.
3. Press with left mouse button on "Module".
4. Paste code to window.
5. Return to Excel.

Back to top

5. Assign macro to a button for quick access

The "Developer" tab allows you to place Form Controls and ActiveX controls on the worksheet, this example demonstrates how to create a button that runs a given macro if the user press with left mouse button ons on it. Form Controls and ActiveX Controls are tools available in Excel  that allow users to create interactive elements like buttons, check boxes, and combo boxes to enhance functionality. Both are used to build user interfaces for macros, dashboards, or data input forms, but they differ in terms of capabilities and usage. The steps below show how to create a button using Form Controls.

1. Go to tab "Developer" on the ribbon.
2. Press with left mouse button on "Insert" button.
3. Press with left mouse button on "Button (Form Control)".
4. Press and hold with the left mouse button on the destination location.
5. Drag with mouse on the sheet to build and control the size of the button.
6. Release the left mouse button.
7. Here is how to assign macro SaveSelectedSheetsToPDF to the button.
8. Press with right mouse button on on the button. A popup menu appears.
9. Press with left mouse button on with left mouse button on "Assign Macro...". A dialog box appears, select the macro you want to assign.
10. Press with left mouse button on the "OK" button on the dialog box to apply changes.
    

Back to top

6. Save the macro in your personal macro workbook

If you save the macro in a personal macro workbook, you can access that macro no matter what workbook you have open.

Read this: Copy your macros to a Personal Macro Workbook

Get the Excel file

Export-all-worksheets-in-workbook-to-PDF-programmaticallyv3.xlsm

Back to top

Macro category

How to create an interactive Excel chart
Table of Contents How to create an interactive Excel chart How to filter chart data How to build an interactive […]

Excel calendar
Table of Contents Excel monthly calendar - VBA  Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]

Split data across multiple sheets – VBA
Table of Contents Split data across multiple sheets - VBA Add values to worksheets based on a condition - VBA […]

Excel categories

Comments (13)