Macro: to print worksheets from different printer trays

[hangtown20]

Trying to create a macro that will print worksheets from different printer trays on the same printer. Any help would be appreciated.[/list]

 

[Smitty]

Welcome to the Board!

Take a look at the PaperSize property in the VBA helpfile.

You can probably use something like this:

Sub PrinterTrays()
    Worksheets("Sheet1").PageSetup.PaperSize = xlPaperLegal
    Worksheets("Sheet2").PageSetup.PaperSize = xlPaperLetter
End Sub

But as my printer only has one tray, I can't really test it.

Hope that helps,

Smitty

 

[colosgps1975]

Print from different trays

I use this trick quite often - I also have a printer with 2 trays, one legal, one letter. I installed my printer a second time in Windows, so it shows up as 2 different printers in the control panel of Windows. Then I set one to default to paper tray 1 and the other to default to paper tray 2. Then I used the macro recorder and recorded my keystrokes to print to 'Printer 1' and again to 'Printer 2'. My code is as follows:

Sub PRINT1()
Application.ScreenUpdating = False
Application.ActivePrinter = "Brother HL-5150D TOP TRAY on Ne01:"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=2, Copies:=1
Application.ScreenUpdating = True
End Sub
Sub PRINT2()
Application.ScreenUpdating = False
Application.ActivePrinter = "Brother HL-5150D BOTTOM TRAY on Ne02:"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1
Application.ScreenUpdating = True
End Sub

Next, I assigned each of these macros to buttons. If I want to print from the top tray, I use the button assigned to 'PRINT1" - bottom tray printing would be 'PRINT2".

Keep in mind that you will need to use this same code in all workbooks where you would be printing from both trays. I hope this helps you -- Good Luck.