How to Save Multiple Excel Sheets as One File (5 Easy Methods)

When saving as a different file format, Microsoft Excel tends to save the current worksheet only in the new file. But there are some handy methods and tricks you can use to save multiple worksheets too. This article will demonstrate how to save multiple Excel sheets as one file regardless of the file type you want to save it in.


Download Practice Workbook

You can download the workbook used for the demonstration and the output file from the boxes below.

This is the workbook we are using for the demonstration.

This is the saved file in pdf.


5 Suitable Methods to Save Multiple Excel Sheets as One File

To save multiple Excel sheets as one file, there are five methods. We can use all of them depending on different scenarios. But some of the methods are less versatile than others. We will go over each of them in its own section of the article. And if you prefer VBA codes, you can get one at the end of the methods to save multiple excel sheets as one file.


1. Using Save As Command

We can use the old school Save As command to easily save multiple excel sheets as one file. Generally, using this command to save an Excel file to other formats result in only the saving of the current sheet to the new format. Instead of just directly saving the file using this command, we can first select multiple or all sheets and then save them to other formats to save all of them as one file.

Follow these steps for a more detailed guide.

Steps:

  • Firstly, select all of the spreadsheets you want to save in one file. To do that you can hold Ctrl on your keyboard and then left-click on the name of the spreadsheet. In case you want to select a series of consecutive spreadsheets, left-click on the first one, and then holding on to the Shift key on your keyboard, click on the last one. This will select all of the spreadsheets in between.

how to save multiple excel sheets as one file

  • Now, select the File tab from your ribbon.
  • Then select Save As from the left side of the backstage view.

how to save multiple excel sheets as one file

  • After that, in the Save As box, navigate to the folder you want to save in and select the file type. Here, we are using pdf format for demonstration.

how to save multiple excel sheets as one file

  • Finally, click on Save.

As a result of these steps, all of the selected spreadsheets will be saved as one pdf or any other format you have selected. Here is the first spreadsheet saved as the first page of the pdf.

This is the second spreadsheet saved on the second page.

This was the third spreadsheet which is saved on the third page here.


2. Utilizing Export Command

Similar to the previous method, you can use a different command Export to save multiple Excel sheets as one file. You can find the command in the same backstage view of the Excel file.

Follow these steps for a more detailed guide.

Steps:

  • First of all, select the spreadsheets from the bottom left of your Excel file by holding Ctrl on your keyboard and left-clicking on the name of the spreadsheets.

how to save multiple excel sheets as one file

  • Then click on the File tab on your ribbon.
  • Now select Export from the left of the backstage view of the file.

how to save multiple excel sheets as one file

  • Next, select the file type from the Export option from the right of the backstage view.

how to save multiple excel sheets as one file

  • Then navigate to the folder you want to save your file in.

how to save multiple excel sheets as one file

  • Finally, click on Publish.

This process will also save multiple selected Excel sheets as one file.

This is the first spreadsheet saved on the first page of our pdf.

Similarly, the second spreadsheet is saved on the second page of the pdf.

In like manner, the third spreadsheet is saved on the third page of the pdf.


3. Using Entire Workbook Option While Saving

Instead of selecting individual spreadsheets before saving them as one file, you can also save all of the sheets from the saving options. Unfortunately, this method can only be used if you want to save all of the spreadsheets in your workbook in different file formats. Follow these steps to see how you can find and use this option.

Steps:

  • First of all, select the File tab from your ribbon.
  • Then select the Save As command from the left side of the backstage view.

how to save multiple excel sheets as one file

  • Now navigate to the folder you want to save your file in. Then select the file type. After that, click on Options.

how to save multiple excel sheets as one file

  • Next, select the Entire Workbook option under the Publish What section.

  • Finally, click on OK and save the file.

This will result in the first spreadsheet on the first page of the pdf.

This will be the second page of the pdf file containing the second spreadsheet of the workbook.

The third page of the pdf will contain the third spreadsheet.

Read More: How to Save a Macro for All Workbooks in Excel (with Easy Steps)


Similar Readings


4. Applying Entire Workbook Option While Exporting as One File

Similar to the previous method, you can find an option to save the entire workbook while exporting the Excel file. Unfortunately, this method only works for saving the entire workbook too. Follow these steps if you want to follow this method.

Steps:

  • Firstly, select the File tab on your ribbon.
  • Secondly, select the Export command from the left of the backstage view.

how to save multiple excel sheets as one file

  • Then select the file type you want to export to from the right side of the view.

how to save multiple excel sheets as one file

  • After that, navigate to the folder you want to export your file to and select the file type. Then click on Options in this box.

how to save multiple excel sheets as one file

  • Now in the Options box that popped up, select Entire workbook under the Publish what section.

  • Finally, click on OK and Publish.

As a result, the first worksheet will be saved on the first page of our pdf.

The second worksheet will be saved on the second page of the pdf.

Similarly, the third spreadsheet will be saved on the third page of the pdf.

Read More: Excel VBA: Save Workbook in Specific Folder (4 Suitable Examples)


5. Embedding VBA Code

You can save multiple Excel sheets as one file using Microsoft Visual Basic for Applications (VBA) too. For that, you need to write down a code that can save the selected spreadsheets to pdf or other formats.

But first, you need to have the Developers tab showing on your ribbon. Enable Developer Tab if you don’t have it on your ribbon. Once you have that, follow these steps to save multiple Excel sheets as one file using VBA.

Steps:

  • First of all, go to the Developers tab on your ribbon.
  • Then select Visual Basic from the Code group.

how to save multiple excel sheets as one file

  • As a result, the VBA window will open up. In the new window, select Insert, and from the drop-down menu select Module.

  • Next, write down the following code in the newly created module. Replace the .pdf with the extension you want.
Sub SaveMultipleSheets()

Dim saveFileLocation As String
saveFileLocation = "C:\Users\user\Documents\Files\Save Multiple Excel Sheets.pdf"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    filename:=saveFileLocation

End Sub
  • Now save the file and select the sheets you want to save.

how to save multiple excel sheets as one file

  • To run the code go to the Developer tab and select Macros from the Code group.

how to save multiple excel sheets as one file

  • Next, select the code name from the Macro box and click on Run.

Consequently, the file will be saved as a pdf or the other format you have selected. This is the first page of the pdf.

The second page containing the second sheet is this.

The third spreadsheet will be on the third page of the pdf file.

Read More: How to Save VBA Code in Excel (3 Suitable Methods)


Save Multiple Sheets from Multiple Workbooks as One File

Now let’s assume a case where you want to save multiple Excel sheets from multiple workbooks in one workbook. Luckily we have a solution for that also. In order to do that we have to manually copy the spreadsheets into the new Excel file. Follow these steps to learn how to do that.

Steps:

  • First, select right-click on the name of the spreadsheet you want to move.
  • Then, select Move or Copy from the context menu.

  • As a result, the Move or Copy box will appear. Now, in the To book option, select (new book) as shown in the figure below. Then check the Create a copy

  • Finally, click on OK.
  • Consequently, a new workbook named Book1 will be created with the file in it. You can rename the file later.
  • Now, repeat all of the steps for all of the spreadsheets you want to save with it. But this time select Book1 instead of creating a new book.

  • Next, select the File tab from your ribbon.
  • Then select the Save As command from the left side of the backstage view.

how to save multiple excel sheets as one file

  • Now navigate to the folder you want to save your file in. Then select the file type. After that, click on Options.

how to save multiple excel sheets as one file

  • Next, select the Entire Workbook option under the Publish What section.

  • Finally, click on OK and save the file.

Hence, all of the spreadsheets will now be saved as one pdf. This is the first spreadsheet on the first page of the pdf.

And this is the second page of the pdf which has the second spreadsheet of the Excel file.


Conclusion

These were all the methods to save multiple Excel sheets as one file. Hope you can easily save your desired sheets in one file. Hopefully, you have found this guide helpful and informative. If you have any questions or suggestions, let us know below.

For more guides like this visit Exceldemy.com.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo