Working with Multiple Printers

You already know that Windows supports multiple printers. Using Excel with multiple printers can be a bother, however, since you must display the Print dialog box (Excel 2007) or the Print information (Excel 2010 or a later version), change the printer, and then print the worksheet.

There is a way, however, that you can have one-click printing of your worksheets on a designated printer. To do this, simply create a macro that changes the printer and then prints the worksheets. Here is a macro that will accomplish the task:

Sub GoodPrinter()
    Application.ActivePrinter = "HP LaserJet"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

When you create this macro on your system, make sure you change the printer name in the second line of the macro. It must exactly match the name of a printer on your system. (In this example the printer name is set to "HP LaserJet". You should change it to match the name of the printer you want used.)

This macro approach changes the name of the printer being used in Excel. If you don't want to disturb whatever printer was active before your macro was run, you can modify it slightly in this manner:

Sub GoodPrinter()
    Dim sPName As String

    sPName = Application.ActivePrinter
    Application.ActivePrinter = "HP LaserJet"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Application.ActivePrinter = sPName
End Sub

The trick is to create one of these macros for each of the printers you use. You can add a command for each printer to your Quick Access Toolbar so that each printer has its own print button. When you then click on the command or button, the appropriate macro is run and you get output on the desired printer.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12578) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Working With Multiple Printers.