Home / Macros / How to print Worksheets to different Printers

How to print Worksheets to different Printers

The ExcelHelpDesk team received the following request for help….

…..”Hello,
I’m working on some automations in Microsoft Office Excel 2007 for the company I’m working for, but I got a problem, which I’ll try to explain to you clearly:

Given:
– There is one Excell file with 3 worksheets: A, B and C.
– There are 2 printers: printer #A# prints on a blank sheet. Printer #B# prints on a sheet with a logo on the top of the sheet.

Question:
How can I print out the Excell file this way?:
– Sheet A is printed by printer #A#
– Sheet B is not printed
– Sheet C is printed by printer #B#
– This complete print task will start after clicking one time on the ‘OK’-button in the printwindow.

Thank you in advance,
Jeroen
“……

The quickest and simplest way to solve this problem is by using a Visual Basic Macro. In this case we need to set specific commands to print Sheet A to one printer and then Sheet C to another printer and not printing Sheet B.

PrintOut Method

Step 1

Open the workbook with the sheets that are to be printed and start the Visual Basic Editor (Alt + F11). Right Click in the project explorer window and select Insert > Module. Copy the following code into the new Module

Sub PrintWorksheets()
 
    ActiveWorkbook.Sheets("Sheet1").PrintOut (ActivePrinter = "Printer Name #A#")
    ActiveWorkbook.Sheets("Sheet3").PrintOut (ActivePrinter = "Printer Name #B#")
 
End Sub

Step 2

Rename “Printer Name #A#” to the required printer name and also “Printer Name #B#”.

Step 3

Now run the Macro by hitting F5 or within the workbook on the Developer Tab, select Macros, then select PrintWorksheets and click on Run. This will then print those worksheets to the specified printers. A further extension would be the creation of a Command button to execute this macro when needed.

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.

About Excel Help Desk

Check Also

CTRL+F1 to Hide the Excel Toolbar using VBA

There are a number of options to solve this problem, the simplest approach we found for this request was the combination of the "SendKeys" function and the Workbook_Activate procedure. Basically we created a routine that would Hide the Toolbar each time the workbook was activated. Then when the workbook was closed or de-activated it would reset the Toolbar to the original state.