Print Multiple Excel Sheets to Single PDF File with VBA (6 Criteria)

When we have so many worksheets in our Excel workbook, sometimes it is convenient to print all those sheets in a single PDF file for better readability. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you 6 different criteria on how to print multiple Excel sheets to a single PDF with the VBA macro.

Print Multiple Excel Sheets to Single PDF with VBA: 6 Criteria

Following this section, we will discuss 6 different criteria on how to print multiple Excel sheets to a single PDF with the VBA.
But first, let us introduce our Excel workbook to you so that you will be able to understand the outcome that we are trying to retrieve for this article.
We have an Excel workbook consisting of three worksheets. The first worksheet name is Sheet1, the data in this sheet are shown in the picture below.

The second worksheet name is Sheet2, the data in this sheet are shown in the following picture.

And the third worksheet name is Sheet3, the data in this sheet are shown in the picture below.

We will learn how to print all these Excel sheets into a single PDF file with VBA.

1. Embed VBA to Print All Sheets of an Excel Workbook in a Single PDF

In this section, you will learn how to print all the sheets (Sheet1, Sheet2, Sheet3) from the Excel workbook (introduced before) into a single PDF file with VBA.

Let’s learn the steps on how to get that.

Steps:

• In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• Next, in the pop-up code window, from the menu bar, click Insert -> Module.

• Then, copy the following code and paste it into the code window.

Sub PrintAllSheetToPdf()
For Each iSheet In ActiveWorkbook.Worksheets
    Worksheets(iSheet.Name).Select False
Next iSheet
With Application.FileDialog(msoFileDialogFolderPicker)
    .Show
    iFolder = .SelectedItems(1) & "\"
End With
iFile = InputBox("Enter New File Name", "PDF File Name")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=iFolder & iFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

Your code is now ready to run.

• Now, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.

• After successful code execution, you will be asked to select the folder where you want to save the PDF file. In our case, we will save the file in the “ExcelDemy” folder in the Local Disk (C:).
• After selecting the new file path, click OK.

• Then, a pop-up input box will appear. It will ask you for a name. Write any name for your new PDF file. In our case, we named our new PDF file “Student Information”.
• Later, click OK.

While you are doing these, you will notice that all the sheets in your workbook are automatically selected (notice in the image below).

• After providing the name for the new PDF file, you will see the newly created PDF file will automatically open.

We named our PDF file “Student Information”. So, if you look at the following gif you will see that the name of the PDF file is “Student Information”, and it has three pages where each page carries the data from each of the worksheets from the Excel workbook. Page 1 of the PDF file holds the data from Sheet1 from the workbook, Page 2 holds the data from Sheet2, and Page 3 holds the data from Sheet3 from the workbook.

Now let’s check whether the “ExcelDemy” folder that we selected as the storage of our PDF file, has the file or not.

• As you can see from the picture below, we have the newly created “Student Information” PDF file in the “ExcelDemy” folder in Drive C.

You want to print the PDF file, right?

• Open the file.
• Then go to File -> Print. Alternatively, you can press Ctrl + P from your keyboard to print the file.
• It will open the Print If you want, then you can modify the page setup according to your need. Then, press Print.

Your newly created PDF file from multiple Excel sheets will start printing. This is how you print all the sheets from the Excel workbook into a single PDF file with VBA.

Sub PrintAllSheetToPdf()

For Each iSheet In ActiveWorkbook.Worksheets
    Worksheets(iSheet.Name).Select False
Next iSheet

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

iFile = InputBox("Enter New File Name", "PDF File Name")

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=iFolder & iFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub

2. Implement VBA to Print Multiple Worksheets from Selection in Excel

In this section, you will learn how to print multiple worksheets by selecting them manually in Excel with VBA.

Let’s see how to achieve that with VBA macro.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, copy the following code and paste it into the code window.

Sub PrintActiveSheetToPdf()
Dim msg As String
Dim iFolder As String
Dim iFile As String
msg = "Do you want to save these worksheets to a single pdf file?" & amp & nbsp & Chr(10)
For Each iSheet In ActiveWindow.SelectedSheets
msg = msg & iSheet.Name & Chr(10)
Next iSheet
iText = MsgBox(msg, vbYesNo, "Confirm to Save as PDF...")
If iText = vbNo Then Exit Sub
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
iFolder = .SelectedItems(1) & "\"
End With
iFile = InputBox("Enter New File Name", "PDF File Name")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=iFolder & iFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

Your code is now ready to run.

• But before running this code, first, go back to the workbook and select all the sheets that you want to export. You can select multiple sheets by clicking on the sheets while holding down the Ctrl key.

• Then, Run the macro.
• There will be a pop-up box, showing you all the sheets that you selected to convert into a single PDF.
• After confirming, click Yes.

• Similar to the previous section, you will be taken to the Browse window where you will be asked to select the folder where you want to save the PDF file. In our case, we will save the file in the “ExcelDemy” folder in the Local Disk (C:).
• After selecting the new file path, click OK.

• Then, a pop-up input box will appear. It will ask you for a name. Write any name for your new PDF file. In our case, we named our new PDF file “Student Information (Selection)”.
• Later, click OK.

• After providing the name for the new PDF file, you will see the newly created PDF file will automatically open.

We named our PDF file “Student Information (Selection)”. So, if you look at the following gif you will see that the name of the PDF file is “Student Information (Selection)”, and it has three pages where each page carries the data from each of the worksheets from the Excel workbook. Page 1 of the PDF file holds the data from Sheet1 from the workbook, Page 2 holds the data from Sheet2, and Page 3 holds the data from Sheet3 from the workbook.

Now let’s check whether the “ExcelDemy” folder that we selected as the storage of our PDF file, has the file or not.

• As you can see from the picture below, we have the newly created “Student Information (Selection)” PDF file in the “ExcelDemy” folder in Drive C.

Now, to print the PDF file, you have to follow the steps below.

• First, open the file.
• Then, go to File -> Print. Alternatively, you can press Ctrl + P from your keyboard to print the file.
• It will open the Print If you want, then you can modify the page setup according to your need. Then, press Print.

Your newly created PDF file from multiple Excel sheets will start printing. This is how you print all the sheets by selection from the Excel workbook into a single PDF file with VBA.

Sub PrintActiveSheetToPdf()

Dim msg As String
Dim iFolder As String
Dim iFile As String

msg = "Do you want to save these worksheets to a single pdf file?" & amp & nbsp & Chr(10)
For Each iSheet In ActiveWindow.SelectedSheets
msg = msg & iSheet.Name & Chr(10)
Next iSheet

iText = MsgBox(msg, vbYesNo, "Confirm to Save as PDF...")
If iText = vbNo Then Exit Sub

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

iFile = InputBox("Enter New File Name", "PDF File Name")

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=iFolder & iFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub

3. Apply VBA Macro to Print Specific Sheets into Single PDF from a Workbook

Till now, we have been learning how to print all the sheets existing in the Excel workbook into a single PDF file with VBA. But what if you don’t want to print all the sheets? What if you want to print some specific sheets from the workbook?

You can do that easily with a few lines of VBA. The steps to get that are given below.

Steps:

• As shown before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, copy the following code and paste it into the code window.

Sub PrintSpecificSheetsToPdf()
    Dim iSheets As Variant
    iSheets = Array("Sheet1", "Sheet2")
    ThisWorkbook.Sheets(iSheets).PrintOut
End Sub
Sub PrintSpecificSheetsToPdfWithLoop()
    Dim iSheets() As String
    Dim iCount As Long
    ReDim iSheets(1 To ThisWorkbook.Sheets.Count)
    For iCount = LBound(iSheets) To UBound(iSheets)
        iSheets(iCount) = ThisWorkbook.Sheets(iCount).Name
    Next iCount
    ThisWorkbook.Sheets(iSheets).PrintOut
End Sub

Your code is now ready to run.

Now, look closely at the code. Do you see the indicated part in the following image? In that line of code, we hardcoded only the specified sheet name – Sheet1 and Sheet2 – that we want to export into PDF.

• Next, Run the macro. You will be briefly notified by a Printing pop-up box.

• You will be brought to the File selection If you want to save the newly created PDF file in the file path that the window is showing, then just simply press OK.

• Or, if you want to store the PDF file at a different address, then click Browse.

• You will be taken to the Save As window, where you get to select the file path that you want the PDF to store. In our case, we will save the file in the “ExcelDemy” folder in the Local Disk (C:). So, we select that.
• After that, you will also be able to name the PDF file. You can either keep the file name that the system is showing you. The name that the system will show you will be the name similar to the Excel file that you are working in. For instance, the Excel sheets (Sheet1, Sheet2, Sheet3) that we are trying to export into PDF, are in the Excel workbook named “Print Multiple Sheets to Single PDF”. That’s why the system is showing this name for the new PDF file in our case.
• After selecting the new file path and providing the file name, click Save.

• Or if you want to save a new name for the PDF file, you can simply just replace the old name with the new one in the File name In our case, we named our new PDF file “Student Information (Specific)”.
• Then, click Save.

• You will be able to see the file path you have just selected with the new file name in the File selection
• Later, click OK.

• After providing the path and the name for the new PDF file, you will see that the newly created PDF file will automatically open.

We named our PDF file “Student Information (Specific)”. So, if you look at the following gif you will see that the name of the PDF file is “Student Information (Specific)” and it has two pages, as we have provided only Sheet1 and Sheet2 in the code. Each page from the PDF carries the data from each of the worksheets specified in the Excel workbook. Page 1 of the PDF file holds the data from Sheet1 from the workbook and Page 2 holds the data from Sheet2 from the workbook.

Now let’s check whether the “ExcelDemy” folder that we selected as the storage of our PDF file, has the file or not.

• As you can see from the picture below, we have the newly created “Student Information (Specific)” PDF file in the “ExcelDemy” folder in Drive C.

Now, to print the PDF file, you have to follow the steps below.

• First, open the file.
• Then, go to File -> Print. Alternatively, you can press Ctrl + P from your keyboard to print the file.
• It will open the Print If you want, then you can modify the page setup according to your need. Then, press Print.

Your newly created PDF file from multiple specified Excel sheets will start printing. This is how you print the multiple specified sheets from the Excel workbook into a single PDF file with VBA.

Sub PrintSpecificSheetsToPdf()

Dim iSheets As Variant

iSheets = Array("Sheet1", "Sheet2")

ThisWorkbook.Sheets(iSheets).PrintOut

End Sub

Sub PrintSpecificSheetsToPdfWithLoop()

Dim iSheets() As String
Dim iCount As Long

ReDim iSheets(1 To ThisWorkbook.Sheets.Count)

For iCount = LBound(iSheets) To UBound(iSheets)
    iSheets(iCount) = ThisWorkbook.Sheets(iCount).Name
Next iCount

ThisWorkbook.Sheets(iSheets).PrintOut

End Sub

4. Embed VBA to Print Multiple Sheets as PDF and Rename According to Cell Value

Until now, you have been manually providing the newly created PDF file name. But this section will teach you how you can print multiple sheets as a single PDF and name the file automatically according to the cell value that you provide in the code.

The steps to get that with the VBA macro are given below.

Steps:

• At first, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, copy the following code and paste it into the code window.

Option Explicit
Public Sub PrintSpecificSheetsToPdfWithRename()
    Dim iSheetList As Variant
    Dim iSheet As Worksheet
    Dim iFileName As String
    Dim iFilePath As String
    Set iSheet = ThisWorkbook.Sheets("Sheet1")
    iSheetList = Array("Sheet1", "Sheet2")
    iFilePath = "C:\ExcelDemy\"
    With iSheet
        iFileName = iFilePath & .Range("B5").Value & " " & .Range("C5").Value & "-" & .Range("D5").Value & ".pdf"
    End With
    ThisWorkbook.Sheets(iSheetList).Select
    iSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=iFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    iSheet.Select
End Sub

Your code is now ready to run.

Now, before executing, look closely at the code. Do you see the indicated part in the following image?

• • In the first line from the indicated part, we provided Sheet1. It means the cell values that we will be taking to name our file, are in that sheet.
  • In the second line, by passing the Sheet1 and Sheet2 inside the array as parameters, we are defining the sheets that will be exported into PDF.
  • In the third line, we wanted to store our PDF file in the Local Drive C inside the “ExcelDemy” folder. You must write the path address where you want your file to save.

Now again, consider the following image to understand the naming system for the PDF file.

In this part of the code, we provided three cell numbers, Cell B5, C5 and D5 from Sheet1. Cell B5 has the value “John Cena”, Cell C5 has the value “101” and Cell D5 has the value “89”. So, the name for the newly created PDF file will be “John Cena 101-89”, we also provided a hyphen (-) between Cell C5 and D5 in the code.

• Now, Run the macro and you will see the newly created PDF file will automatically open.

If you look at the following gif you will see that the name of the PDF file is “John Cena 101-89” and it has two pages, as we have provided only Sheet1 and Sheet2 in the code. Each page from the PDF carries the data from each of the worksheets specified in the Excel workbook. Page 1 of the PDF file holds the data from Sheet1 from the workbook and Page 2 holds the data from Sheet2 from the workbook.

Now let’s check whether the “ExcelDemy” folder that we hardcoded as the storage of our PDF file, has the file or not.

• As you can see from the picture below, we have the newly created “John Cena 101-89” PDF file in the “ExcelDemy” folder in Drive C.

Now, we want to print the file, right? To print the PDF file, you have to follow the steps below.

• First, open the file.
• Then, go to File -> Print. Alternatively, you can press Ctrl + P from your keyboard to print the file.
• It will open the Print If you want, then you can modify the page setup according to your need. Then, press Print.

Your newly created PDF file from multiple hardcoded Excel sheets will start printing. This is how you print the multiple specified sheets from the Excel workbook into a single PDF file and name it according to the cell values from a sheet with VBA.

Option Explicit

Public Sub PrintSpecificSheetsToPdfWithRename()

Dim iSheetList As Variant
Dim iSheet As Worksheet
Dim iFileName As String
Dim iFilePath As String

Set iSheet = ThisWorkbook.Sheets("Sheet1")

iSheetList = Array("Sheet1", "Sheet2")

iFilePath = "C:\ExcelDemy\"

With iSheet
    iFileName = iFilePath & .Range("B5").Value & " " & .Range("C5").Value & "-" & .Range("D5").Value & ".pdf"
End With

ThisWorkbook.Sheets(iSheetList).Select
    iSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=iFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    iSheet.Select

End Sub

5. Apply Macro to Export Multiple Excel Sheets to PDF inside a Created Folder

Are you bored of providing the folder path every time you run the code? Do you want to save the PDF file inside a folder that you create via macro code? Then follow this section with full concentration.

Steps:

• Firstly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Secondly, copy the following code and paste it into the code window.

Sub PrintSheetsToPdfInFolder()
Dim iFolderAdrs As String
iFolderAdrs = "C:\ExcelDemy\New Student Information"
MkDir iFolderAdrs
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=iFolderAdrs & "\Student Information", OpenAfterPublish:=False, IgnorePrintAreas:=False
MsgBox "All worksheets are successfully exported into a single pdf!"
End Sub

Your code is now ready to run.

• After that, Run the macro. You will be notified by a pop-up MsgBox after successful file conversion.
• From there, just simply press OK.

Now, before going further, let’s describe a couple of things first. See the indicated part of the code in the image below?

In that line of the code, we did the tricks. We store the path – C:\ExcelDemy – where we want to save the PDF file and following that we provided a name – “New Student Information” – as the new folder name in our case. You must write the path address according to your data and you can provide any name that you want as your new folder name.

Now again, consider the following image.

• • In the first indicated part, we stored the sheets named – Sheet1, Sheet2 and Sheet3 – that we want to export as PDF. If you want, then you can add more sheets to that line or remove any sheets from that line.
  • In the second indicated part, we provided a new file name – “Student Information” – for our PDF file. You can write any name that you want.

Now that you have understood the key things of the code, let’s check whether the newly created folder that we hardcoded as “New Student Information” in the C:\ExcelDemy path, is created or not.

• As you can see from the picture below, we have the newly created folder “New Student Information” in the “ExcelDemy” folder in Drive C.

As we have successfully created a folder to store our PDF file, now it’s time to check whether the folder has the file in it or not.

• Open the folder.
• And as you can see from the picture below, we have the newly created PDF file “Student Information” inside the “New Student Information” folder that resides in the “ExcelDemy” folder in Drive C.

Let’s open the PDF file and check whether it has all the sheets that we provided in the code in it or not.

When we open the file, “Student Information”, we saw that it has three pages where each page carries the data from each of the worksheets from the Excel workbook. Page 1 of the PDF file holds the data from Sheet1 from the workbook, Page 2 holds the data from Sheet2 and Page 3 holds the data from Sheet3 from the workbook.

Now, to print the PDF file, you have to follow the steps below.

• First, open the file.
• Then, go to File -> Print. Alternatively, you can press Ctrl + P from your keyboard to print the file.
• It will open the Print If you want, then you can modify the page setup according to your need. Then, press Print.

Your newly created PDF file from multiple hardcoded Excel sheets inside the new folder will start printing. This is how you print the multiple sheets from the Excel workbook into a single PDF file and store it inside a folder that you create with VBA.

Sub PrintSheetsToPdfInFolder()

Dim iFolderAdrs As String

iFolderAdrs = "C:\ExcelDemy\New Student Information"

MkDir iFolderAdrs

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=iFolderAdrs & "\Student Information", OpenAfterPublish:=False, IgnorePrintAreas:=False

MsgBox "All worksheets are successfully exported into a single pdf!"

End Sub

6. Implement VBA to Export Active Worksheet to PDF Multiple Times in the Working Folder

This section will teach you how you can save the active Excel worksheet into a single PDF file multiple times with different names with VBA.

The steps to execute that are given below.

Steps:

• At first, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• After that, copy the following code and paste it into the code window.

Sub PrintSpecificSheetsToPdfInCurrentPath()
Dim iSheet As Worksheet
Dim iBook As Workbook
Dim iFileName As String
Dim iFilePath As String
Dim iFile As String
Dim iPathFile As String
Dim NewFile As Variant
Dim msg As Long
On Error GoTo errHandler
Set iBook = ActiveWorkbook
Set iSheet = ActiveSheet
iFilePath = iBook.Path
If iFilePath = "" Then
  iFilePath = Application.DefaultFilePath
End If
iFilePath = iFilePath & "\"
iFileName = iSheet.Range("B6").Value & " - " & iSheet.Range("C6").Value & " - " & iSheet.Range("D6").Value
iFile = iFileName & ".pdf"
iPathFile = iFilePath & iFile
If iOldFile(iPathFile) Then
  msg = MsgBox("Replace current file?", vbQuestion + vbYesNo, "Existing File!")
  If msg <> vbYes Then
    NewFile = Application.GetSaveAsFilename(InitialFileName:=iPathFile, FileFilter:="PDF Files (*.pdf), *.pdf", Title:="Enter folder and filename to save")
    If NewFile <> "False" Then
      iPathFile = NewFile
    Else
      GoTo exitHandler
    End If
  End If
End If
iSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=iPathFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
MsgBox "New PDF file is created: " & vbCrLf & iPathFile
exitHandler:
    Exit Sub
errHandler:
    MsgBox "There is an error while creating PDF file!"
    Resume exitHandler
End Sub
Function iOldFile(rsFullPath As String) As Boolean
  iOldFile = CBool(Len(Dir$(rsFullPath)) > 0)
End Function

Your code is now ready to run.

• Then, Run the macro and the file path and the name for your newly created PDF file will be shown to you via Excel MsgBox.

To understand how we got the name and the file path, you have to look at the following image.

See the indicated part in the above image. This did the trick of naming the file.

In this part of the code, we provided three cell numbers – Cell B6, C6 and D6 – from the active sheet, Sheet1. Cell B6 has the value “Roman Reigns”, Cell C6 has the value “102” and Cell D6 has the value “56”. So, the name for the newly created PDF file is “Roman Reigns – 102 – 56”, we also provided a hyphen (-) between Cell B6, C6 and D6 in the code.

The path we got is none other than the path where we have the source Excel workbook. We have our workbook in the C:\ExcelDemy path, so the newly created PDF file “Roman Reigns – 102 – 56” is also stored in the exact same address.

• Now, Run the macro.

Now, let’s check whether the path has the file in it or not.

• As you can see from the picture below, the source Excel file – “Print Multiple Sheets to Single PDF” – and the newly created PDF file from it – “Roman Reigns – 102 – 56” – both are in the “ExcelDemy” folder in Drive C.

If you open the file, you will see that the PDF has only one page and the data on that page is the exact data from the active sheet, Sheet1 from the workbook.

Now, we want to print the file, right? To print the PDF file, you have to follow the steps below.

• First, open the file.
• Then, go to File -> Print. Alternatively, you can press Ctrl + P from your keyboard to print the file.
• It will open the Print If you want, then you can modify the page setup according to your need. Then, press Print.

Now, what if you need to modify the Excel sheet and re-print it again? What if you want to keep both the old PDF file and the new PDF file?

• Run the code again.
• There will be a pop-up MsgBox asking you whether you want to replace the old file with the new file or not.
• If you want to replace the old file, then simply click Yes.

• It will automatically replace your old PDF file created from the Excel worksheet with the new one.

• But if you want to keep both PDF files, then click NO in the pop-up MsgBox.

• This will take you to the Browse window, where you will be able to select the path and rename the file according to your requirements. In our case, we store the new PDF file in the C:\ExcelDemy path and renamed the file “Roman Reigns – 102 – 56 Part 2”.
• Click Save.

Now go back to the file directory to check whether there are multiple copies of the same PDF file with different names or not.

• As you can see from the picture below, both files – “Roman Reigns – 102 – 56” and “Roman Reigns – 102 – 56 Part 2” – are in the “ExcelDemy” folder in Drive C.

This is how you print an active Excel sheet to a PDF and rename it multiple times with VBA.

Sub PrintSpecificSheetsToPdfInCurrentPath()

Dim iSheet As Worksheet
Dim iBook As Workbook
Dim iFileName As String
Dim iFilePath As String
Dim iFile As String
Dim iPathFile As String
Dim NewFile As Variant
Dim msg As Long

On Error GoTo errHandler

Set iBook = ActiveWorkbook
Set iSheet = ActiveSheet

iFilePath = iBook.Path
If iFilePath = "" Then
  iFilePath = Application.DefaultFilePath
End If
iFilePath = iFilePath & "\"

iFileName = iSheet.Range("B6").Value & " - " & iSheet.Range("C6").Value & " - " & iSheet.Range("D6").Value

iFile = iFileName & ".pdf"
iPathFile = iFilePath & iFile

If iOldFile(iPathFile) Then
  msg = MsgBox("Replace current file?", vbQuestion + vbYesNo, "Existing File!")
  If msg <> vbYes Then
    NewFile = Application.GetSaveAsFilename(InitialFileName:=iPathFile, FileFilter:="PDF Files (*.pdf), *.pdf", Title:="Enter folder and filename to save")
    If NewFile <> "False" Then
      iPathFile = NewFile

Else
      GoTo exitHandler
    End If
  End If
End If

iSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=iPathFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

MsgBox "New PDF file is created: " & vbCrLf & iPathFile

exitHandler:
    Exit Sub

errHandler:
    MsgBox "There is an error while creating PDF file!"
    Resume exitHandler

End Sub

Function iOldFile(rsFullPath As String) As Boolean
  iOldFile = CBool(Len(Dir$(rsFullPath)) > 0)
End Function

Download Workbook

You can download the free practice Excel workbook from here.

Conclusion

To conclude, this article showed you 6 different criteria on how to print multiple Excel sheets to a single PDF with the VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.

Related Articles

• Print Range to PDF with VBA in Excel
• Print to PDF Using Macro Button in Excel
• Excel VBA to Print As PDF and Save with Automatic File Name
• Excel VBA: ExportAsFixedFormat PDF with Fit to Page
• How to Print to PDF in Excel VBA : With Examples and Illustrations