Excel gives you a lot of options when you’re trying to print your work. You can choose to print the entire worksheet, a specific area in the worksheet, print multiple sheets, or all sheets at one go.
In this tutorial, I will show you how you can print multiple sheets in Excel at one go. These could be some selected sheets or all the sheets in the workbook.
And in case you want to print a specific area in multiple/all sheets, you can do that too with a little bit of VBA magic.
So let’s get started!
Print All Sheets at One Go
Excel has an inbuilt feature that allows you to specify to print all the sheets at one go.
Below are the steps to print all the sheets in the workbook:
- Click the File tab
- Click on the Print option
- In the Print page, click on the Print setting drop-down
- Click on Print Entire Workbook
- Click on Print
The above steps would print all the sheets in the workbook. In case you have a print area set in some of the sheets, then only that print area will be printed.
You can also see what will be printed in the Print preview on the right. You can also change page numbers and see what will be printed on each page.
Easy enough!
Now, what if you only want to print only some specific sheets and not the entire workbook.
Read on!
Print Multiple Sheets (Selected Ones) at One Go
This is again quite easy to achieve.
All you need to do is selected those specific sheets that you want to print and then print it!
Below are the steps to print some specific sheets in a workbook in Excel:
- Select all the sheets that you want to print. To do this, hold the Control key and select sheets one by one. In this example, I am selecting Sheet 1, 4 and 5
- Click the File tab
- Click on the Print option
- In the Print page, click on the Print setting drop-down
- Click on Print Active Sheets (in most cases, it’s already the default option, but in case it isn’t you can choose that from the drop-down)
- Click on Print
When you select multiple sheets, these all act as active sheets while printing.
You can also see what will be printed in the Print preview on the right. You can also change page numbers and see what will be printed on each page.
Print Multiple Sheets With a Specific Print Area
This one is a little more complex than the previous two.
Suppose you have a workbook with multiple sheets, and you want to print a specific area from each sheet.
Maybe there is summary data in each sheet and you only want to print this data and not the entire worksheet.
This can be done by setting a print area in all the sheets and then printing these (as shown in the above two methods).
Now when it comes to setting the print area:
- You need to do it manually for each sheet (especially if the print area is different for each sheet)
- Or you can use a simple VBA code to set the same print area in all the sheets at one go.
Once you have set the print area, you can then use any of the above methods to print the sheets.
So let me quickly show you how to set the Print area manually and using VBA.
Setting the Print Area manually
Below are the steps to do this:
- Select the cells that you want to be covered in the print area
- Click the ‘Page Layout’ tab
- In the Page Setup group, click on ‘Print Area’
- Click on ‘Set Print Area’
That’s it!
This would set the print area to the selected cells and when you print this sheet, only the print area will be printed.
You need to do this manually for each sheet. So if you want to print specific areas in Sheet1, Sheet4, and Sheet5, you will have to do it for each sheet separately.
Setting the Print Area using VBA
In case you have a lot of worksheets, setting the print area manually can be time-consuming.
In that case, you can also use VBA to quickly set the print area in one sheet, and then run the code to replicate it to all the other sheets.
Note: This method works well when you have the same range of cells that you want to use while setting the Print Area.
Below is the VBA macro code that will do this:
Sub SetPrintAreas1()
Dim PrntArea As String
Dim ws As Worksheet
PrntArea = ActiveSheet.PageSetup.PrintArea
For Each ws In Worksheets
ws.PageSetup.PrintArea = PrntArea
Next
Set wks = Nothing
End Sub The above code uses the print area from the active sheets, goes to all the sheets in the workbook and sets the same print area in each of these sheets.
Once you have this set, you can now print all the sheets (or some selected sheets), and only the print area will be printed.
You can put this VBA macro code in a regular module and run it from there.
So these are some scenarios where you can print multiple sheets in Excel in one go.
Hope you found this tutorial useful!
You may also like the following Excel tutorials:
1 thought on “How to Print Multiple Sheets (or All Sheets) in Excel in One Go”
I’m sure it is because I’m new to VBA, but I’ll ask a stupid question. Don’t you have to designate a range of cells to set a print area in each worksheet? If that’s what you are trying to accomplish.