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

1. Export all worksheets in workbook to PDF programmatically

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?

Back to top

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

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

2.2 VBA code

Sub SaveSelectedSheetsToPDF()
Dim str As String, myfolder As String, myfile As String

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

answer = MsgBox(str, vbYesNo, "Continue with save?")
If answer = vbNo Then Exit Sub

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

myfile = InputBox("Enter filename", "Save as..")

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

End Sub

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

Explaining the macro

A macro starts with Sub and then the macro name

Sub SaveSelectedSheetsToPDF()

Declaring variables

Variables str, myfolder and myfile are declared data type String.
Read more about Defining data types.

Dim str As String, myfolder As String, myfile As String

Concatenate text strings 

The selected sheet names are concatenated with a question, Chr(10) is a carriage return.

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

Message box  

The message box lets you confirm you have selected the correct sheets. If the answer is no the macro ends.

answer = MsgBox(str, vbYesNo, "Continue with save?")
If answer = vbNo Then Exit Sub

Choose a directory
The directory is saved in the string variable myfolder.

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

Enter a file name
The file name is saved in the string variable myfile.

myfile = InputBox("Enter file name", "Save as..")

Create pdf
The selected sheets are saved as a pdf file with the name myfile and path myfolder.

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

End a macro
A macro ends with this statement.

End Sub

Back to top

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

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