Print Button to print with different Printer types for different sheet

bootdat

New Member
Joined
Nov 22, 2018
Messages
44
I have 3 different sheets with a print button assigned to a macro.

two of these sheets, i want the macro to use Microsoft Print to PDF to print out the printing area
and i want the other sheet to use an EPSON Thermal printer

My problem is, when i select the Microsoft print to PDF as the printer, all three sheets prints with that
and when i also select the EPSON Thermal printer as the printer, all three sheets prints with that

I want my micros to know which Printer to use depending on the sheet I'm printing from

VBA Code:
ActiveSheet.ExportAsFixedFormat _
    Type:=xlTpePDF, _
    Filename:="C:\XX\mod\" & Range("E9").Value & "_" & Range("H6").Value & ".pdf", _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

Thats the vba code i use for the printing

Thank you
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

severynm

Board Regular
Joined
Jan 8, 2021
Messages
64
Office Version
  1. 365
Platform
  1. Windows
To do that, all you need to do is change the active printer before you print out. To set the active printer you need both the printer name and the port name as a string (reference). The easiest way to do that is to set the active printer via the normal print menu, then type Debug.Print Application.ActivePrinter in the immediate window (ctrl+g from the code editor). It should return something like PrinterName on port:. For example, ET0021B7E2F9D1 on Ne04: and CutePDF Writer on CPW3: are two of mine. From there, all you need to do to print to the desired printer is set (for example)
VBA Code:
Application.ActivePrinter = "ET0021B7E2F9D1 on Ne04:"

I'm not sure exactly, but the way it sounds like you have your macro set up is you have a button on each of those three sheets which all call the same piece of code. Getting the source of where that function was called is a little more difficult. From my experience, you have two options:
  1. You can make three different functions (one for each button) that only differ in the printer they set as Active.
  2. If you are using ActiveX buttons and not form controls (the difference), you can use the _Click method of the buttons to call your print code in a different module and pass a parameter of the current worksheet. I.e.
VBA Code:
Private Sub Button1onSheet1_Click()    
    Module1.PrintToCorrectPrinter Me.Name
End Sub

And your printing code is then
VBA Code:
Public Sub PrintToCorrectPrinter(sheetName As String)
    If sheetName = "xxxx" Then
        Application.ActivePrinter = "ET0021B7E2F9D1 on Ne04:"
    ElseIf sheetName = "xxxx" Then
         Application.ActivePrinter = "CutePDF Writer on CPW3:"
    Elseif ...
         ....
    End If

    Sheet1.PrintOut
End Sub
 
Solution

bootdat

New Member
Joined
Nov 22, 2018
Messages
44
Hi.. thanks for your response

I'm try that out and give a feedback.

Thanks

To do that, all you need to do is change the active printer before you print out. To set the active printer you need both the printer name and the port name as a string (reference). The easiest way to do that is to set the active printer via the normal print menu, then type Debug.Print Application.ActivePrinter in the immediate window (ctrl+g from the code editor). It should return something like PrinterName on port:. For example, ET0021B7E2F9D1 on Ne04: and CutePDF Writer on CPW3: are two of mine. From there, all you need to do to print to the desired printer is set (for example)
VBA Code:
Application.ActivePrinter = "ET0021B7E2F9D1 on Ne04:"

I'm not sure exactly, but the way it sounds like you have your macro set up is you have a button on each of those three sheets which all call the same piece of code. Getting the source of where that function was called is a little more difficult. From my experience, you have two options:
  1. You can make three different functions (one for each button) that only differ in the printer they set as Active.
  2. If you are using ActiveX buttons and not form controls (the difference), you can use the _Click method of the buttons to call your print code in a different module and pass a parameter of the current worksheet. I.e.
VBA Code:
Private Sub Button1onSheet1_Click()   
    Module1.PrintToCorrectPrinter Me.Name
End Sub

And your printing code is then
VBA Code:
Public Sub PrintToCorrectPrinter(sheetName As String)
    If sheetName = "xxxx" Then
        Application.ActivePrinter = "ET0021B7E2F9D1 on Ne04:"
    ElseIf sheetName = "xxxx" Then
         Application.ActivePrinter = "CutePDF Writer on CPW3:"
    Elseif ...
         ....
    End If

    Sheet1.PrintOut
End
[/QUOTE]
 

bootdat

New Member
Joined
Nov 22, 2018
Messages
44
To do that, all you need to do is change the active printer before you print out. To set the active printer you need both the printer name and the port name as a string (reference). The easiest way to do that is to set the active printer via the normal print menu, then type Debug.Print Application.ActivePrinter in the immediate window (ctrl+g from the code editor). It should return something like PrinterName on port:. For example, ET0021B7E2F9D1 on Ne04: and CutePDF Writer on CPW3: are two of mine. From there, all you need to do to print to the desired printer is set (for example)
VBA Code:
Application.ActivePrinter = "ET0021B7E2F9D1 on Ne04:"

I'm not sure exactly, but the way it sounds like you have your macro set up is you have a button on each of those three sheets which all call the same piece of code. Getting the source of where that function was called is a little more difficult. From my experience, you have two options:
  1. You can make three different functions (one for each button) that only differ in the printer they set as Active.
  2. If you are using ActiveX buttons and not form controls (the difference), you can use the _Click method of the buttons to call your print code in a different module and pass a parameter of the current worksheet. I.e.
VBA Code:
Private Sub Button1onSheet1_Click()   
    Module1.PrintToCorrectPrinter Me.Name
End Sub

And your printing code is then
VBA Code:
Public Sub PrintToCorrectPrinter(sheetName As String)
    If sheetName = "xxxx" Then
        Application.ActivePrinter = "ET0021B7E2F9D1 on Ne04:"
    ElseIf sheetName = "xxxx" Then
         Application.ActivePrinter = "CutePDF Writer on CPW3:"
    Elseif ...
         ....
    End If

    Sheet1.PrintOut
End Sub


Hi, sorry for the late response...

so it happened that all i had to do was to set the desired printer.

so i added
VBA Code:
Application.ActivePrinter = "EPSON TM-T20II Receipt5 on Ne05:" 'use here your printer
to the code where the document is needed to be printed on that printer

finalizing it as:
VBA Code:
Application.ActivePrinter = "EPSON TM-T20II Receipt5 on Ne05:" 'use here your printer
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTpePDF, _
    Filename:="C:\XX\mod\" & Range("E9").Value & "_" & Range("H6").Value & ".pdf", _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

and did same to the other code where i need it to print as Microsoft Print to PDF

VBA Code:
Application.ActivePrinter = "Microsoft Print to PDF on Ne01:" 'use here your printer
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTpePDF, _
    Filename:="C:\XX\mod\" & Range("E9").Value & "_" & Range("H6").Value & ".pdf", _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False


so that worked for me

thanks very much..
 

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,336
Members
417,021
Latest member
moon miner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top