Sabrina Ayon
Microsoft Excel allows us to print the data as per our wish. There are so many options available while printing multiple sheets at a time in excel. . In this article, we will demonstrate how to print multiple sheets in Excel.
Download Practice Workbook
You can download the workbook and practice with them.
7 Different Methods to Print Multiple Sheets in Excel
Excel has a built-in feature with which we can print the whole workbook together. There are so many ways we can print excel multiple sheets. So, let’s have a look at all the different ways to print multiple sheets in excel.
1. Print All Sheets at Once in Excel
1.1. Print All Sheets at Once
Suppose, we have four sheets in our workbook and we want to print all the sheets in one go. To print all sheets at once. Simply follow the steps below.
STEPS:
- First, go to the File tab on the ribbon or press Ctrl + P. This will take us to the main menu.
- After that, click on the Print option.
- Next, from the Print option, go to the print Settings drop-down menu then, select Print Entire Workbook.
- After that just click on the Print button.
- And, there you go!
By following these instructions, you can print the entire workbook in just one go.
1.2. VBA to Print the Entire Workbook
We can use VBA Macros to print the entire workbook at once. For this, we need to follow the procedure down.
STEPS:
- Firstly, go to the Developer tab on the ribbon.
- Secondly, click on Visual Basic to open the Visual Basic Editor where we will write the VBA codes.
- Another way to open the Visual Basic Editor is simply to press Alt + F11.
- Or, instead of opening the editor from the Developer tab, you can click on any sheet on your spreadsheet then right-click. Select the View Code option.
- And, this will open up the Visual Basic Editor.
- Next, go to Insert and select Module from the drop-down menu.
Suggestion: The code can not be written on any sheet. You’ll need to insert a Module to create the code because we’ll utilize it across the entire spreadsheet, not just one sheet. When we need to write a code for a specific sheet, we can use the sheets to do so.
- After that, copy and paste the VBA code below.
VBA Code:
Sub Print_Workbooks()
ActiveWorkbook.PrintOut
End Sub- Next, press the F5 key or click on the Run Sub button to run the code.
- Finally, by running the code, we can easily print the entire workbook.
1.3. Print Multiple Sheets Using VBA
We can also print multiple sheets using the VBA Macros.
STEPS:
- In the beginning, likewise, the previous method, to open the Visual Basic Editor, first go to the Developer tab on the ribbon.
- Click on Visual Basic or press Alt + F11 to open the Visual Basic Editor.
- Another way to open the Visual Basic Editor is, simply right-click on any sheet and select View Code.
- Next, go to Insert and select Module from the drop-down menu.
- Now, write down the below VBA Code.
VBA Code:
Sub Print_Multiple()
Worksheets(Array("Cars", "Furniture")).PrintOut
End Sub- Finally, Run the code by clicking the Run Sub button, on the other hand, press the keyboard shortcut F5 key to run the code.
- In the end, running the code will print multiple sheets together.
Read More: Print Multiple Excel Sheets to Single PDF File with VBA (6 Criteria)
2. Active Multiple Sheets Printing
2.1. Print Active Sheets
Assume that, we have four sheets of data but there are only two active sheets and we want to print only those active sheets together. To do this, let’s demonstrate the steps down.
STEPS:
- First, select all the sheets we want to print. To do so, hold down the Ctrl key and choose one sheet at a time.
- Second, go to the File tab on the ribbon or press Ctrl + P.
- After that, click on the Print option.
- Next, from the Print option, go to the print Settings drop-down menu then, select Print Active Sheets.
- And finally, click on Print.
2.2. VBA to Print only the Active Sheets
By using Excel VBA Macros, we can print the active sheets from our workbook. To do this, follow the steps below.
STEPS:
- Firstly, go to the Developer tab on the ribbon.
- Secondly, click on Visual Basic or press Alt + F11.
- Or, we can simply right-click on the sheet and select View Code.
- This will open up the Visual Basic Editor.
- Go to Insert and select Module from the drop-down menu.
- Now, write down the VBA Code.
VBA Code:
Sub Print_ActiveSheets()
ActiveSheet.PrintOut
End Sub- Next, run the code by pressing F5 or clicking the Run Sub button.
This will print all the active sheets on the spreadsheet.
Read More: How to Print Excel Spreadsheet on Multiple Pages (3 Ways)
3. Print a Specific Print Area on Many Sheets
3.1. Manually Adjusting the Print Area
Suppose, we have multiple sheets of a spreadsheet, but we want to print a specific area from each workbook. We can do that by just following the procedure below.
STEPS:
- In the first place, select the cells that we want to print for each sheet.
- In the second place, go to the Page Layout tab on the ribbon.
- Next, click on the Print Area drop-down menu under the Page Setup group.
- After that, click on the Set Print Area.
- And that’s it! This will only give the printout for the selected specific area.
3.2. Using VBA to Set the Print Area
VBA can also help to print the selected area. To use the VBA Macros follow the steps down.
STEPS:
- By the same token, go to the Developer tab on the ribbon. Then, click on Visual Basic.
- Right-click on the sheet, then select View Code.
- This will take you to the Visual Basic Editor field, where we can write VBA Macros.
- Now, go to Insert and select Module from the drop-down menu.
- Here, write down the below VBA Code.
VBA Code:
Sub Print_Multiple()
Dim PMultiple As String
Dim ws As Worksheet
PrntArea = ActiveSheet.PageSetup.PrintArea
For Each ws In Worksheets
ws.PageSetup.PrintArea = PMultiple
Next
Set wks = Nothing
End Sub- Then, run the code by clicking on the Run Sub button or using the keyboard shortcut F5 key.
- This will print multiple worksheets but only the selected print area on each sheet.
Read More: How to Print Selected Area in Excel (2 Examples)
4. VBA to Print Multiple Excel Sheets on One page
By using the VBA Macros we can print multiple sheets on a single sheet. Simply just follow the below instructions.
STEPS:
- In the beginning, go to the Developer tab > Visual Basic > Insert > Module.
- Or, right-clicking on the worksheet will open up a window. From there go to the View Code.
- And, this will take you to the Visual Basic Editor field, where we can write VBA Macros.
- On the other hand, pressing Alt + F11 will also open the Visual Basic Editor.
- Now, copy and paste the VBA code that is shown below.
VBA Code:
Sub Print_Sheets_on_SinglePage()
With Worksheets("Cars").PageSetup
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
End With
End Sub- Next, run the code by clicking on the Run Sub button or using the keyboard shortcut F5 key.
- And finally, when you print the worksheet ‘Cars’, the printer will give you the whole workbook printed at once.
Read More: How to Print Sheet on One Page in Excel (9 Easy Ways)
Similar Readings:
- How to Keep Header in Excel When Printing (3 Ways)
- How to Set a Row as Print Titles in Excel (4 Methods)
- Excel VBA: How to Set Print Area Dynamically (7 Ways)
- How to Print Excel Sheet in A4 Size (4 Ways)
- How to Not Print Empty Cells in Excel (3 Simple Methods)
5. Multiple Sheets with Comments to Print in Excel
We can print multiple sheets with comments using VBA Macros. Let’s see the procedure below.
STEPS:
- First, go to the Developer tab from the ribbon.
- Second, click on Visual Basic to open the Visual Basic Editor.
- Another way to open the Visual Basic Editor is simply to press Alt + F11.
- Or, right-click on the sheet, then select View Code.
- Next, go to Insert and select Module from the drop-down menu.
- And, this will open up the Visual Basic Editor.
- After that, copy and paste the VBA code.
VBA Code:
Sub Print_with_Comnts()
Application.DisplayCommentIndicator = xlCommentAndIndicator
With ActiveSheet
.PageSetup.PrintComments = xlPrintInPlace
.PrintOut
End With
End Sub- Finally, press the F5 key or click on the Run Sub button to run the code.
- In the end, we can see the required result.
Read More: How to Print Worksheet with Comments in Excel (5 Easy Ways)
6. Print Selected Excel Sheets
With Excel VBA, we can also print selected sheets from our workbook.
STEPS:
- To begin, click the Developer tab on the ribbon.
- Second, open the Visual Basic Editor by clicking on Visual Basic.
- Alternatively, we can access the Visual Basic Editor by pressing Alt + F11.
- Rather, right-click on the sheet and choose View Code from the menu.
- Next, select Module from the drop-down menu under Insert.
- The Visual Basic Editor will launch as a result of this.
- Copy and paste the VBA code below after that to see the final output.
VBA Code:
Sub Print_SelectedSheets()
ActiveWindow.SelectedSheets.PrintOut
End Sub- Finally, run the code by pressing F5 or clicking the Run Sub button.
Read More: How to Print Selected Cells in Excel (2 Easy Ways)
7. Print Multiple Hidden Worksheets
Sometimes, our sheets could be hidden and we are not able to print only those files, but with Excel VBA this is easy to do.
STEPS:
- To begin, use the ribbon to navigate to the Developer tab.
- Second, open the Visual Basic Editor by selecting Visual Basic.
- We can also enter the Visual Basic Editor by pressing Alt + F11.
- Or, right-click on the sheet and choose View Code.
- Then, from the Insert drop-down menu, pick Module.
- The Visual Basic Editor will then open.
- Then copy and paste the VBA code from below.
VBA Code:
Sub Print_Hidden_sheets()
Dim Vis As Long
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
With sh
Vis = .Visible
.Visible = xlSheetVisible
.PrintOut
.Visible = Vis
End With
Next sh
End Sub- In the end, press the F5 key or click on the Run Sub button to run the code. And we can see the output.
Related Content: How to Print Excel Sheet with Lines (3 Easy Ways)
Conclusion
The above methods assist you to Print Multiple Sheets in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!
