Print 1 worksheet from multiple printer trays using VB macro

justneedtoprint

New Member
Joined
Jul 2, 2019
Messages
4
I have no coding experience, but hoping this is a really easy fix. Here's what I'd like to do:

Print worksheet from multiple printer trays at once (network printer). Right now, I have to hit "print", then do it again, but change the output tray. I print from 4 different trays every time I need to print. I tried recording a macro, but it won't record that I'm changing printer trays on the network printer.

No cell range or anything needed. Just need to print the same workbook out of 4 different printer trays, so I have multiple copies on different colored paper. The below VB works for me in MS Word, but I'm getting a "Runtime Error 424: Object Required" error every time I try and run it in Excel. Any help is greatly appreciated.

Sub PrintTwoTrays()
Dim sTray As String

sTray = Options.DefaultTray
Options.DefaultTray = "Tray 1"
Application.PrintOut FileName:=""
Options.DefaultTray = "Tray 2"
Application.PrintOut FileName:=""
Options.DefaultTray = sTray
End Sub
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

justneedtoprint

New Member
Joined
Jul 2, 2019
Messages
4
Bump. Anyone know how to achieve this?

I have no coding experience, but hoping this is a really easy fix. Here's what I'd like to do:

Print worksheet from multiple printer trays at once (network printer). Right now, I have to hit "print", then do it again, but change the output tray. I print from 4 different trays every time I need to print. I tried recording a macro, but it won't record that I'm changing printer trays on the network printer.

No cell range or anything needed. Just need to print the same workbook out of 4 different printer trays, so I have multiple copies on different colored paper. The below VB works for me in MS Word, but I'm getting a "Runtime Error 424: Object Required" error every time I try and run it in Excel. Any help is greatly appreciated.

Sub PrintTwoTrays()
Dim sTray As String

sTray = Options.DefaultTray
Options.DefaultTray = "Tray 1"
Application.PrintOut FileName:=""
Options.DefaultTray = "Tray 2"
Application.PrintOut FileName:=""
Options.DefaultTray = sTray
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Try this

Code:
Sub PrintTwoTrays()
    Dim sh As Worksheet, sTray As String
    Set sh = ActiveSheet
    
    sTray = Application.ActivePrinter
    
    Application.ActivePrinter = "Tray 1"
    sh.PrintOut
    Application.ActivePrinter = "Tray 2"
    sh.PrintOut
    
    Application.ActivePrinter = sTray
End Sub
 

justneedtoprint

New Member
Joined
Jul 2, 2019
Messages
4
Thank you, I appreciate it. Getting Runtime error 1004 (Method 'ActivePrinter' of object '_Applicaton' failed) when I try the below. I am so frustrated, seems this should be such a simple thing...


Try this

Code:
Sub PrintTwoTrays()
    Dim sh As Worksheet, sTray As String
    Set sh = ActiveSheet
    
    sTray = Application.ActivePrinter
    
    Application.ActivePrinter = "Tray 1"
    sh.PrintOut
    Application.ActivePrinter = "Tray 2"
    sh.PrintOut
    
    Application.ActivePrinter = sTray
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Thank you, I appreciate it. Getting Runtime error 1004 (Method 'ActivePrinter' of object '_Applicaton' failed) when I try the below. I am so frustrated, seems this should be such a simple thing...

In which line of the macro does it stop?

You must put the full name of your printers. For example:
"OKI B4350 PS (MS) in Ne01:"

"Ne01" It's the name of the port

1. Execute the following code.
2. In the printer list window select one and OK.
Repeat the steps until you have all the full names in column B.

Code:
Sub getPrint()
    Application.Dialogs(xlDialogPrinterSetup).Show
    Range("B" & Rows.Count).End(xlUp)(2) = Application.ActivePrinter
End Sub


Then replace those names in the code.
Code:
 Application.ActivePrinter = "[COLOR=#ff0000]Tray 1[/COLOR]"
 sh.PrintOut
 Application.ActivePrinter = "[COLOR=#ff0000]Tray 2[/COLOR]"
 

justneedtoprint

New Member
Joined
Jul 2, 2019
Messages
4
Thank you. I understand what you're getting at below, but the problem is that only allows me to select the printer, but NOT the tray I want to print from. I am using the same printer for all 4 trays I want to print out of. So, replacing "Tray1", "Tray2" with the full printer name won't work.

In which line of the macro does it stop?

You must put the full name of your printers. For example:
"OKI B4350 PS (MS) in Ne01:"

"Ne01" It's the name of the port

1. Execute the following code.
2. In the printer list window select one and OK.
Repeat the steps until you have all the full names in column B.

Code:
Sub getPrint()
    Application.Dialogs(xlDialogPrinterSetup).Show
    Range("B" & Rows.Count).End(xlUp)(2) = Application.ActivePrinter
End Sub


Then replace those names in the code.
Code:
 Application.ActivePrinter = "[COLOR=#ff0000]Tray 1[/COLOR]"
 sh.PrintOut
 Application.ActivePrinter = "[COLOR=#ff0000]Tray 2[/COLOR]"
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Thank you. I understand what you're getting at below, but the problem is that only allows me to select the printer, but NOT the tray I want to print from. I am using the same printer for all 4 trays I want to print out of. So, replacing "Tray1", "Tray2" with the full printer name won't work.

One solution is to set up the same printer twice with different predetermined trays and call the printers accordingly.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,359
Messages
5,601,153
Members
414,431
Latest member
JustmemyselfandI

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top