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