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

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.

7 Different Methods to Print Multiple Sheets in Excel

  • Next, from the Print option, go to the print Settings drop-down menu then, select Print Entire Workbook.

7 Different Methods to Print Multiple Sheets in Excel

  • After that just click on the Print button.
  • And, there you go!

7 Different Methods to Print Multiple Sheets in Excel

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.

7 Different Methods to Print Multiple Sheets in Excel

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

7 Different Methods to Print Multiple Sheets in Excel

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.

7 Different Methods to Print Multiple Sheets in Excel

  • Another way to open the Visual Basic Editor is, simply right-click on any sheet and select View Code.

7 Different Methods to Print Multiple Sheets in Excel

  • Next, go to Insert and select Module from the drop-down menu.

7 Different Methods to Print Multiple Sheets in Excel

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

7 Different Methods to Print Multiple Sheets in Excel

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

7 Different Methods to Print Multiple Sheets in Excel

  • Or, we can simply right-click on the sheet and select View Code.

7 Different Methods to Print Multiple Sheets in Excel

  • This will open up the Visual Basic Editor.
  • Go to Insert and select Module from the drop-down menu.

7 Different Methods to Print Multiple Sheets in Excel

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


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!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo