How to save specific multiple worksheets to a pdf file programmatically

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

What's on this webpage

1. Export all worksheets in workbook to PDF programmatically
2. How to save specific multiple worksheets to a pdf file programmatically
3. Where to put the code?
4. Assign macro to a button for quick access
5. Save the macro in your personal macro workbook
6. Get Excel 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

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.

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.

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.

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

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.

Get the Excel file

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

Macro category

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

Save invoice data – VBA
Table of contents Save invoice data - VBA Invoice template with dependent drop down lists Select and view invoice - […]

Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]