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.

Save Multiple Excel Sheets as One File: 5 Suitable Methods

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

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

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.

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.

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

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

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

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.

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

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.

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

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

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.

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.

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

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.

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

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 a Worksheet in Excel

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.

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

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.

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.

Save Multiple Sheets as One File.xlsm

This is the saved file in pdf.

Save Multiple Sheets as One File.pdf

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.

<< Go Back to How to Save Excel File | Excel Files | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!