Printing to different local printers

[Bobk2023]

I am new to VBA and I am trying to print sheet 1 to a Brother QL-600 label printer, Sheet 2 to a Dymo label printer, and sheet 3 to a Brother L2710 without having to go to printer select each time

Thanks for your help

Bob

 

[John_w]

Welcome to MrExcel forums.

Try this macro, ensuring that the specified printer names are the actual Windows printer names.

Public Sub Print_Sheets_On_Specific_Printers()

    Dim CurrentPrinterNameNet As String
    
    'Save current printer
    
    CurrentPrinterNameNet = Application.ActivePrinter
    
    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
    
    'Restore current printer
    
    Application.ActivePrinter = CurrentPrinterNameNet

End Sub


'Written: November 28, 2009
'Author:  Leith Ross
'Summary: Finds a printer by name and returns the printer name and port number.

Public Function FindPrinter(ByVal PrinterName As String) As String

    'This works with Windows 2000 and up
    
    Dim Arr As Variant
    Dim Device As Variant
    Dim Devices As Variant
    Dim printer As String
    Dim RegObj As Object
    Dim RegValue As String
    Const HKEY_CURRENT_USER = &H80000001
    
    Set RegObj = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    RegObj.enumValues HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Devices, Arr
    
    For Each Device In Devices
        RegObj.getstringvalue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Device, RegValue
        printer = Device & " on " & Split(RegValue, ",")(1)
        'If InStr(1, Printer, PrinterName, vbTextCompare) > 0 Then  'original code
        If StrComp(Device, PrinterName, vbTextCompare) = 0 Then
            FindPrinter = printer
            Exit Function
        End If
    Next
      
End Function

 

[portews]

Utility to find the printer names:

Sub PrinterNames()
Dim Printer As String

bOK = Application.Dialogs(xlDialogPrinterSetup).Show      'choose the printer
     If bOK = False Then Exit Sub
Printer = InputBox("Printer name for this computer", "Get Printer Names", Application.ActivePrinter)

End Sub