save multiple excel sheets to a single pdf file

This macro saves multiple selected sheets to a single pdf file.

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

Make sure you select the sheets you want to save as a single pdf file before you run the macro. Press and hold CTRL and then left click with mouse on multiple sheets to select them.

This happens when you start the macro

  1. Confirm selected sheets by clicking "Yes"
    save multiple excel sheets to a single pdf file1
  2. Choose a save folder
  3. Enter a file name
    save multiple excel sheets to a single pdf file2
  4. Click OK
  5. A pdf file is created and opens

Where do I put the code in my workbook?

  1. Start the VB Editor (Alt+F11)
  2. Click "Insert" on the menu
  3. Click "Module"
  4. Paste code to window
  5. Return to excel

save multiple excel sheets to a single pdf file3

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

Assign macro to a button for quick access

save multiple excel sheets to a single pdf file4

  1. Go to tab "Developer" on the ribbon
  2. Click "Insert" button
  3. Click "Button (Form Control)
  4. Click and drag on sheet to build a button
  5. Assign macro SaveSelectedSheetsToPDF
  6. Click OK

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

Download excel *.xlsm file

Combine separate sheets into one pdf file (vba).xlsm