How to save specific multiple worksheets to a pdf file programmatically

This article demonstrates macros that save worksheets to a single pdf file.

1. Export all worksheets in workbook to PDF programmatically

Exporting all worksheets in a workbook to PDF programmatically can be useful for several reasons:

1. Batch processing: Automating the export process saves time and effort, especially when dealing with large workbooks or multiple workbooks.
2. 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.
3. Sharing and collaboration: PDFs are widely supported and can be easily shared with others, making it a convenient format for collaboration and review.
4. 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.
5. Reporting: PDFs can be used to generate reports that can be easily distributed to stakeholders, such as management, customers, or regulatory bodies.
6. 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.
7. Security: PDFs show only the selected worksheets, not the formulas and data behind the formulas.

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

The following macro saves all worksheets in the active workbook to a single pdf file.

'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

Where to put the code?

1.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.

1.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

2. How to save specific multiple worksheets to a pdf file programmatically

'Name macro
Sub SaveSelectedSheetsToPDF()

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

'Get names from worksheets
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 Message box
answer = MsgBox(str, vbYesNo, "Continue with save?")
If answer = vbNo Then Exit Sub

'Pick a directory
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
myfolder = .SelectedItems(1) & "\"
End With

'Ask for folder
myfile = InputBox("Enter filename", "Save as..")

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

'Quit macro
End Sub

2.1 How to use the macro?

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

3. 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

4. Assign macro to a button for quick access

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 with left mouse button on and drag on sheet to build a button
5. Assign macro SaveSelectedSheetsToPDF
6. Press with left mouse button on OK

Back to top

5. 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)