Print 1 worksheet from multiple printer trays using VB macro

[justneedtoprint]

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

 

[justneedtoprint]

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]

Try this

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]

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

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]

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.

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

Then replace those names in the code.

 Application.ActivePrinter = "[COLOR=#ff0000]Tray 1[/COLOR]"
 sh.PrintOut
 Application.ActivePrinter = "[COLOR=#ff0000]Tray 2[/COLOR]"

 

[justneedtoprint]

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.

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

Then replace those names in the code.

 Application.ActivePrinter = "[COLOR=#ff0000]Tray 1[/COLOR]"
 sh.PrintOut
 Application.ActivePrinter = "[COLOR=#ff0000]Tray 2[/COLOR]"

 

[DanteAmor]

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.