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.


Download Workbook

You can download the free practice Excel workbook from here.


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

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.

Sheet one for Print Multiple Excel Sheets to Single PDF File with VBA

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

Sheet two for Print Multiple Excel Sheets to Single PDF File with VBA

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

Sheet 3 for Print Multiple Excel Sheets to Single PDF File with VBA

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.

Print All Multiple Excel Sheets to Single PDF File with VBA

  • 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.

Select folder for Print All Multiple Excel Sheets to Single PDF File with VBA

  • 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.

Select name for Print All Multiple Excel Sheets to Single PDF File with VBA

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.

Result of Print All Multiple Excel Sheets to Single PDF File with VBA

Now let’s check whether the “ExcelDemyfolder 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 “ExcelDemyfolder 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.

VBA Code Explanation

Sub PrintAllSheetToPdf()

Name the sub-procedure of the macro.

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

Select all worksheets in the active workbook.

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

Ask for a directory to save the newly created PDF file in.

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

Ask for a name for the newly created PDF file.

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

Save all worksheets from the active workbook as a single PDF file

  • In the path address and with the file name
  • With the Standard
  • With the Word file properties.
  • Without the Print Areas.
  • Will automatically open after the code execution.
End Sub

Ends the sub-procedure of the macro.

Read More: How to Print All Sheets in Excel (3 Methods)


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.

Print Multiple Excel Sheets to Single PDF File from selection with VBA

  • 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.

Select multiple sheets to Print Multiple Excel Sheets to Single PDF File with VBA

  • 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 “ExcelDemyfolder 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.

Result of Print Multiple Excel Sheets to Single PDF File from selection with VBA

Now let’s check whether the “ExcelDemyfolder 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 “ExcelDemyfolder 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.

Print Multiple Excel Sheets from selection to Single PDF File with VBA

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.

VBA Code Explanation

Sub PrintActiveSheetToPdf()

Name the sub-procedure of the macro.

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

Declare the variables.

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

Select only the selected worksheets in the active workbook. Show the sheet names concatenated with a question in a MsgBox to confirm. Chr(10) is a carriage return.

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

Execute the task according to Yes or No confirmation. If Yes, then continue with the procedure. If No, then exit the procedure.

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

Ask for a directory to save the newly created PDF file in.

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

Ask for a name for the newly created PDF file.

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

Save all worksheets from the active workbook as a single PDF file

  • In the path address and with the file name
  • With the Standard
  • With the Word file properties.
  • Without the Print Areas.
  • Will automatically open after the code execution.
End Sub

Ends the sub-procedure of the macro.

Read More: How to Print Multiple Sheets in Excel (7 Different Methods)


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.

Print Multiple Specific Excel Sheets to Single PDF File with VBA

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 nameSheet1 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.

Browse to Print Multiple specific Excel Sheets to Single PDF File with VBA for path selection

  • 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 “ExcelDemyfolder 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.

Selecting name for Print Multiple specific Excel Sheets to Single PDF File with VBA

  • 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.

selecting path for Print Multiple specific Excel Sheets to Single PDF File with VBA

  • 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.

Result of Print Multiple Specific Excel Sheets to Single PDF File with VBA

Now let’s check whether the “ExcelDemyfolder 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 “ExcelDemyfolder 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.

Print Multiple Specific Excel Sheets to Single PDF File with VBA

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.

VBA Code Explanation

Sub PrintSpecificSheetsToPdf()

Name the sub-procedure of the macro.

Dim iSheets As Variant

Declare the variable to store the multiple sheets.

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

Store the sheets that will be exported in the declared array variable.

ThisWorkbook.Sheets(iSheets).PrintOut

Prints the group of sheets with the PrintOut function.

End Sub

Ends the sub-procedure of the macro.

Sub PrintSpecificSheetsToPdfWithLoop()

Name the sub-procedure of the macro that will perform the loop operation.

Dim iSheets() As String
Dim iCount As Long

Declare the variables.

ReDim iSheets(1 To ThisWorkbook.Sheets.Count)

Re-declare the array variable. This time it stores the number of the total sheet count in it.

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

This part of the code starts iterating from the smallest subscript to the largest subscript of the array and stores the array value in the variable. It continues doing this until it finishes scanning through the whole array.

ThisWorkbook.Sheets(iSheets).PrintOut

Prints the group of sheets with the PrintOut function.

End Sub

Ends the sub-procedure of the macro.

Read More: How to Print Specific Sheets Using VBA Macro in Excel (4 Ways)


Similar Readings:


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.

Print Multiple Excel Sheets to Single PDF File with VBA and rename

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 “ExcelDemyfolder. 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.

Result of Print Multiple Excel Sheets to Single PDF File with VBA and rename

Now let’s check whether the “ExcelDemyfolder 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 “ExcelDemyfolder 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.

Print Multiple Excel Sheets to Single PDF File with VBA and rename to 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.

VBA Code Explanation

Option Explicit

Forces to declare all the variables explicitly of the file.

Public Sub PrintSpecificSheetsToPdfWithRename()

Name the sub-procedure of the macro.

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

Declares all the variables.

Set iSheet = ThisWorkbook.Sheets("Sheet1")

Stores the sheet from where we will be taking the cell values to name our PDF file.

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

Declares the sheets that will be exported into PDF.

iFilePath = "C:\ExcelDemy\"

Declares the directory to store our PDF file.

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

Selects the sheet and takes values from Cell B5, C5 and D5 to name the PDF file. Then exits the sheet.

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

Save the hardcoded worksheets from the active workbook as a single PDF file

  • In the path address and with the file name
  • With the Standard
  • With the Word file properties.
  • Without the Print Areas.
  • Will automatically open after the code execution.
End Sub

Ends the sub-procedure of the macro.

Read More: How to Print Selected Cells in Excel (2 Easy Ways)


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.

Print Multiple Excel Sheets to Single PDF File with VBA with folder

  • 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 “ExcelDemyfolder in Drive C.

Print Multiple Excel Sheets to Single PDF File with VBA in created folder

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 Informationfolder that resides in the “ExcelDemyfolder in Drive C.

Print Multiple Excel Sheets to Single PDF File with VBA file in folder

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.

Result of Print Multiple Excel Sheets to Single PDF File with VBA in folder

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.

Print Multiple Excel Sheets to Single PDF File with VBA and print it from folder

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.

VBA Code Explanation

Sub PrintSheetsToPdfInFolder()

Firstly, provide a name for the sub-procedure of the macro.

Dim iFolderAdrs As String

Declare the variable to carry the path address.

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

Stores the directory and declares a new folder name.

MkDir iFolderAdrs

Creates the new folder with Excel’s MkDir statement.

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

Selects the sheets and save them as a single PDF file

  • In the path address and with the file name – “\Student Information” – provided.
  • Without the Print Areas.
  • Turn off the automatic file opening after the code execution.
MsgBox "All worksheets are successfully exported into a single pdf!"

Confirmation message after successfully file creation.

End Sub

Ends the sub-procedure of the macro.

Related Content: How to Print Excel Sheet with Header on Every Page in Excel (3 Methods)


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.

Print Multiple Excel Sheets to Single PDF File with VBA and replace old file

  • 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 “ExcelDemyfolder 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.

Result of Print Multiple Excel Sheets to Single PDF File with VBA in the working folder

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.

Print Multiple Excel Sheets to Single PDF File with VBA in the working folder to 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.

Print Multiple Excel Sheets to Single PDF File with VBA and replace with new file

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 “ExcelDemyfolder in Drive C.

Old and new file for Print Multiple Excel Sheets to Single PDF File with VBA

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

VBA Code Explanation

Sub PrintSpecificSheetsToPdfInCurrentPath()

At first, name the sub-procedure of the macro.

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

Then, declare the variables.

On Error GoTo errHandler

If any error occurs, then go to errHandler statement.

Set iBook = ActiveWorkbook
Set iSheet = ActiveSheet

Later, store the active workbook and worksheet in the variables.

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

Gets the active workbook folder if the file is saved.

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

After that, it takes values from Cell B6, C6 and D6 to name the PDF file and store the name in the variable.

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

Later, creates a default name for the new file to save.

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

After that, it performs the file replacement operation. If the file already exists, then save the file with a new path and name. If the file doesn’t exist, then save the file in the current directory.

Else
      GoTo exitHandler
    End If
  End If
End If

Otherwise, go to the exitHandler statement in the code.

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

Save the worksheet from the active workbook as a single PDF file

  • In the path address and with the file name
  • With the Standard
  • With the Word file properties.
  • Without the Print Areas.
  • Turn off the automatic file opening after the code execution.
MsgBox "New PDF file is created: " & vbCrLf & iPathFile

Confirmation message after successfully file creation.

exitHandler:
    Exit Sub

Code in the exitHandler statement, to exit the sub-procedure.

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

Code in the errHandler statement, to notify that there is an error. Go back to the exitHandler statement.

End Sub

Finally, ends the sub-procedure of the macro.

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

Lastly, calls the function to execute all the operations that we just explained.

Read More: How to Print Excel Spreadsheet on Multiple Pages (3 Ways)


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

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo