In Excel (2007) VBA how do I print the worksheet to a specific printer tray? This will just print to the default tray on the printer:
ws.PrintOut ActivePrinter:="\\print_server\printer_name"
Microsoft offers one ugly solution to this problem; using SendKeys to manually choose the correct tray from the print settings dialog box.
Another option may be to set up additional printers in Windows, where each printer is actually linked to a different tray on the same printer. You could then select the appropriate tray in VBA. This article has the details.
Or, if your printer happens to have a different size of paper in the tray you want to print to, you can set (for example):
ws.PageSetup.PaperSize = xlPaperLegal
And the printer will automatically select the correct tray. I just tried this out on my printer here, and it forced the sheet to print on the manual tray, because I don't actually have a legal size paper tray.