ExcelInsider Team
Technically, we seldom work with small datasets or single sheets in the real world. We often need to work with multiple Excel sheets in one file for financial summaries, departmental reports, and invoices. That’s not the issue; the main problem is to share such files with the clients or the team members. It is not a good idea to send sheets separately, time and time again. As a result, we often struggle with how to save multiple Excel sheets as one PDF. However, it is not a big deal if you know the right steps.
Follow the steps below if you want to save multiple Excel sheets as one PDF.
➤ Go to the File and click on the Save As option to save the file.
➤ Choose the Browse option to save it to your preferred location.
➤ In the Save As Type text, select PDF as the file type.
➤In the Options, mark the Entire Workbook portion and press OK.
In this article, we will explore all the ins and outs of saving multiple sheets in one PDF and discuss different ways. From the basic Save As options to the optimized VBA and Power Automate, we will delve into the basic to advanced approach. Sit tight to know all the methods in detail.
Use The Built-in Save as PDF Option
The most basic and straightforward approach is using the basic Save As option in Microsoft Excel. Fortunately, it can transform your entire workbook containing separate sheets into one PDF. It presents one sheet per page most of the time, unless it contains many columns.
Steps:
➤ Open the Excel file that needs to be saved as PDF.
➤ Go to File -> Save As option.
➤ Click on the Browse option or choose the file location you want to save.
➤ Type the name in the name field, and select PDF format in the Save as Type.
➤ In the lower portion of the window, there are options for Optimized for.
➤ Click on Options to open a new small window containing Page range, Publish what, etc.
➤ Mark the Entire Workbook under the Publish What option.
➤ Click OK to close the Options window.
➤ Finally, click on Save to save it.
Note:
➨ This method will serialize the sheets as the worksheets you have created. Rearrange them before saving to choose the order in which they appear.
➨ Ensure all the data has proper layout and margin and is print-ready.
Export Multiple Sheets with Excel’s Print to PDF Option
Sometimes, the basic Save As option may not work due to the improper layout. As a result, your preserved scaling may get distorted. In that case, the Print to PDF option in Excel can be a savior. Without much extra knowledge, you can easily convert your sheets using this approach.
Steps:
➤ Open the workbook and go to File Tab -> Print.
➤ Under the Printer option, choose Microsoft Print to Print from the dropdown menu.
➤ Below it, in the Settings option, the default settings are Print Active Sheets to Print Entire Workbook.
➤ Review the page layout and sheet structure from the preview on the right-hand side.
➤ If everything feels okay, hit the Print button.
➤ Give the file name and choose the file location.
➤ With the Save button, the file will be automatically saved to the PDF option.
Note:
➨ Don’t save the workbook before going to the print option.
➨ To change the page layout and margin, choose the options under settings.
Automate Multi-Sheet PDF Export Using Excel VBA
Though you can easily convert Excel to PDF by the Save As or Print-to-PDF option, you can’t choose which sheets to include. By default, it saves all the sheets in one PDF. VBA Macros can be a life-changing solution for converting selected sheets. A short and straightforward VBA script can do the work.
Here, we assume we want to include only Sheets 1, 3, and 5: Sales Summary, Monthly Expenses, and the Client Contact List, respectively.
Steps:
➤ Open your Excel file and go to Developer -> Visual Basic.
➤ This will launch the VBA editor window.
➤ On that new window, click the Insert tab and select Module from the drop-down menu.
➤ Paste the following code in the space below.
Sub ExportSelectedSheetsToPDF()
Dim wsArray As Variant
wsArray = Array("Sales Summary", "Monthly Expenses", "Client Contact List")
Sheets(wsArray).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=ThisWorkbook.Path & "\SelectedSheets.pdf"
End Sub
➤ Save the VBA script and close the window.
➤ Now, click on the Macros option beside the Visual Basic.
➤ In the Macro window, select the macro name. In this script, the name is ExportSelectedSheetsToPDF (the first line of the VBA script).
➤ As you click Run, a PDF is automatically saved in the same folder as the Excel file with the required sheets.
Note:
The selected file names are given in the VBA script line number 3 –
wsArray = Array(“Sales Summary”, “Monthly Expenses”, “Client Contact List”)
Change the name of the sheets inside the parentheses to your sheet name. It is case-sensitive, so maintain proper casing of the letters to avoid errors.
Save Selected Sheets as One PDF Without Code
VBA Macros can be daunting for newbies, especially when saving selected sheets. But the catch is that you can also do this manually with Excel’s grouping sheets feature. It is faster and more efficient, and can complete the process with a few easy clicks.
Steps:
➤ In your Excel file, press Ctrl and click on the sheets you want to combine into a PDF.
➤ Go to File Tab -> Save As option.
➤ Browse and save your file with the desired name and location.
➤ In the file type, choose PDF, which will generate new options below the window.
➤ Click the Options button to ensure Active Sheets are marked under the Publish What section.
➤ Click OK to close the window and finally hit Save.
Note:
Grouped sheets are presented in the order you have selected them.
Frequently Asked Questions
How do I combine only specific sheets into one PDF?
To combine only specific sheets into one PDF, it is best to use the Sheet Grouping method or VBA Macros. You can customize your VBA script or select the sheets by pressing the Ctrl key to save them as a single PDF.
Why is only one sheet saving as a PDF in Excel?
The Save As option is built to only save the active sheet as one PDF. If you want to save all the sheets, go to the option in the Save As window and choose the Entire Workbook.
Can I rearrange the order of sheets in a PDF?
You can rearrange the order of sheets while saving them as a PDF. If you use the general Save As option, rearrange the cells beforehand to save them as your required option. In VBA and Sheet Grouping, maintain the order as you click or write the script for proper order.
Can multiple sheets be saved to one PDF on Excel Online or Google Sheets?
Both Excel Online and Google Sheets can save multiple sheets as a single PDF.
Can I save Excel charts from multiple sheets into one PDF?
Excel charts can be saved from multiple sheets into one PDF. You can have the sheets contain the chart and save those with VBA Macros or Sheet Grouping. To avoid the grouping hassle, move all the charts to a single sheet and save them.
How can PDF export in Excel be automated without VBA?
You can use the built-in Export option of Excel or the Save As button to export Excel files as PDF.
Concluding Words
Saving multiple sheets in one PDF is mundane, yet we all struggle with this. Depending on your use, now you know how to use Save As, Print-to-PDF, Sheet Gouping, and even automated VBA for an easy and faster solution. As each method serves different scenarios – from smaller datasets to enterprise-level works – Excel provides a unified approach for every possible way without any other apps. Now, it’s your turn to pick the right one for you.
