Printer tray selection in VBA for printing excel sheet

[Jici142]

Hi,

I'm using a template to print label and each sheet need a different label type from Avery. I recently installed a new printer with 5 trays.

Now I want a VBA sub to print the sheet and make sure it is printed from the right tray. I did configured some printer profiles too which auto-select the pre-defined tray.

Thanks for helping.

JC

 

[John_w]

See if Printing to different local printers helps.

 

[Jici142]

thanks for reference, but it is not clear from this example how could I specify the tray to use...

 

[John_w]

You add a new Windows printer for Tray 1, with the printer properties specifically set to use Tray 1. Do the same for the other trays, giving each Windows printer a different name.

This part of the code specifies which Windows printer to use:

    With ThisWorkbook
        .Worksheets("Sheet1").PrintOut ActivePrinter:=FindPrinter("Brother QL-600"), Copies:=1, IgnorePrintAreas:=False
        .Worksheets("Sheet2").PrintOut ActivePrinter:=FindPrinter("Dymo"), Copies:=1, IgnorePrintAreas:=False
        .Worksheets("Sheet3").PrintOut ActivePrinter:=FindPrinter("Brother L2710"), Copies:=1, IgnorePrintAreas:=False
    End With

Change the printer names according to your printer names.