Author: Oscar Cronquist Article last updated on April 14, 2021

Save selected sheets to a pdf file

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 left click 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 clicking "Yes".
    save multiple excel sheets to a single pdf file1
  2. A dialog box appears, choose a save folder. Click OK.
  3. A new dialog box appears. Enter a file name.
    save multiple excel sheets to a single pdf file2
  4. Click 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?

save multiple excel sheets to a single pdf file3

  1. Start the Visual Basic Editor (Alt+F11).
  2. Click "Insert" on the top menu, see image above.
  3. Click "Module".
  4. Paste code to window.
  5. Return to Excel.
Note, save your workbook with file extension *.xlsm (macro-enabled workbook) to attach the code.

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

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

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

Back to top