Email:      Pass:    Pass?


Advertisements


Free Excel Forum

Printing To A Specific Printer

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Morning all, hoping somebody could assist with a small query I have.

I have a workbook with a button that allows me to select worksheets that have been filled out and send them to the printer. At the moment it will print to whatever the default printer is set to. I use the below code to print the active worksheet;

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Is it possible I can specify what printer to use. I have tried using the 'To' command but this just does nothing.

I also need to be able to specify what sheets to print out. Is it possible to print a sheet by its name?

Thanks for any help in advance.

Jon


Similar Excel Video Tutorials

Helpful Excel Macros

Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Print Preview Screen Display for The Current or Selected Worksheets in Excel
- This free Excel macro allows you to display the print preview screen or window for the current or selected worksheets in
Print Selected Worksheets in Excel
- This free excel macro will print all selected worksheets in Excel. If there are no other worksheets selected the active
Display the Print Window in Excel
- This free Excel macro displays the print window or dialog box in Excel. This is the same window that would appear when
Print Preview Screen Display for The Entire Workbook in Excel
- This free Excel macro allows you to quickly and easily display the print preview window or mode for the entire Excel wor

Similar Topics







I need my spreadsheet to print to a printer that has labels in them.


Code:

Sub LabelPrint()

    Application.ActivePrinter = "\\Sidonna\HP DeskJet 895Cse on Ne05:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub


I think this will force this file to print to the specific printer. Is there a way for this to pause before it prints though? Maybe with a message box that says continue to print or cancel print.


Thank You,
Michael


When you press CTRL + P you get a print setup page where you can select the printer etc. I want to write a macro to select a specific array of worksheets and then pull up this box. When I try to use the

Code:

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True


command it just prints directly to the printer. The problem with this is that there will be many different users and all have different printers installed. I am afraid the code will inevitably fail with at least one combination of PC and printer. Therefore I want to give the user te ability to select a printer to print out the data being selected by the macro.

Any suggestions?


Hi I want to write a simple macros to print only certain worksheets with a workbook.

It is easy enough to Record a Macro to do this, i.e., print workshhet 2 and worksheet 3 within a standard 3 worksheet workbook. My question is: How to write a Macro that will print the specified sheets on any PC that may be aligned with a unique printer?

The macro below works, and I imagine it will work on any other PC aligned with the same printer. However, what if the workbook is opened on another PC with a different default printer or another PC that has the same printer with the printer not being connected, i.e., a laptop without wireless printing and the USB unplugged.

There has to be an easy way to tell Excel to pick X # of worksheets and then que the print box associated with PC wherein the user then chooses his or specified printer and then clicks print. The below works, but when opened on a laptop with the same printer unplugged, it sends the specified worksheets to the print que with an obvious failure to print The pause before the actual pint is missing.

Looking for code that chooses the worksheets needed and then ques the user to click print with appropriate printer? Maybe this is setting the Print area to worksheets 2 & 3 or is there another comman versus Printout Copies shown below???

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/4/2011 by User
'
'
Sheets(Array("Sheet2", "Sheet3")).Select
Sheets("Sheet2").Activate
ActiveWindow.SelectedSheets. PrintOut Copies :=1, Collate:=True
Sheets(Array("Sheet2", "Sheet3")).Select
Sheets("Sheet3").Activate
Range("A2").Select
End Sub



I have a small macro that is attached to a print button on sheet 1 that
prints the graphs on sheet 2.

My problem is that I wish to print to a printer that is not the default
printer. Can the printer be selected automatically when the macro runs.

This is what I have in the macro at present and the name of the printer is "

Sub Print_Graphs()
Sheets("Graphs").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Data Sheet").Select
Range("B18").Select
End Sub






I have 6 printers at work that I print to. I have my own printer set as the default.

When I print to printer A, I would like my default printer set as the printer that would print the next "quick" document. However it will print to printer A until I switch it back to my printer.

This becomes a problem when I print payroll documents to someone else's printer.

There must be a way that when I print to a printer that Excel automatically changes the default printer back to my printer.

Can anyone help me??


In Excel, I created a macro to print to my network printer (at work) so I could just click one button and it would print. (easy)
The button works no problem when that printer is already setup on my machine. What I'm trying to accomplish is having that button print to about 12 printers in my company at the same time by just pressing one button, and not having to set up each printer on my machine.
Is this possible?

Specs a
LAN
Win 2000
Printer urls are \\servername\printer

sample code for printer already mapped:

Application.ActivePrinter = "\\f0632p1\0632COPY on Ne01:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\f0632p1\0632COPY on Ne01:", Collate:=True
End Sub


Thanks,
Brad


Hi there,

I just registered on this forum, hoping someone can help me at the following issue;

I would like to have the ability to set a printername/number in cell A1, and use this cell as the name for the printer to where my printjobs get sent.

This way, I can set the printername, and use a button which activates a macro to print to a specific printer.

So far, I have this code, executed on buttonpress:
Code:

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
        "\\printserver\printer1 on Ne13:", Collate:=True


What I would like to have (but working) is:
Code:

    
ActivePrinter:=  "\\printserver\" & Worksheets("Sheet1").A1 
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True





I can run:

Sheets("MORNING REPORT").PrintOut Copies:=4, Collate:=True

and it will print the MORNING REPORT sheet to the default SELECTED printer. I want to develop a macro that will print the MORNING REPORT sheet to the printer that is default to the system Excel is running on.

We all know that when Excel prints a WS to the default SELECTED printer and if the system excel is running on is hooked to a network the printed WS could end up in another office/bldg etc.

What I would like to do is open a WB and have it print a specific sheet to the printer that is default to that system.

ANY and ALL help appreciated!

I hope everyone has a great Thanksgiving day!

And, most importantly, I want to thank ALL of you for the help and support you give us "non" excel types.....

cliff


I have been using a macro in a couple of workbooks that allows the user to print several pages from multiple printers. The macro changes printers and prints selected sheets, and then changes back to the users default printer when complete. The problem is that the Ne01, 02 etc as shown below is different on each computer based upon how many printers they have installed. (I think) I need to find a way that will either have the macro print to a certain printer without using the Ne01,02,etc. or have some type of loop that looks at each Ne (Say Ne01-Ne15 or so) until it finds the correct one.

I've done some searching but have not found exactly what I need. I have some example code shown below. (I modified if for simplification.)

Thank you for your help!

'* * * Record current default printer * * *
strActPrt = Application.ActivePrinter

'* * * Change default printer to COLOR-BC * * *
Application.ActivePrinter = "\\mfmain\COLOR-BC on Ne01:"

'* * * Print TOTALS sheet * * *
Sheets("TOTALS").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

'* * * Change Printer to RPT_ACCT * * *
Application.ActivePrinter = "\\mfmain\RPT_ACCT on Ne02:"

'* * * Print FINAL sheet * * *
Sheets("FINAL").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

'* * * Change back to default printer * * *
Application.ActivePrinter = strActPrt


I have written a macro to print to a specific printer on the network. I started by using the macro recorder to see what Excel liked for the printer name, and then added code as necessary. It is a very simple macro that defines a print range, then prints to the specified printer as follows:

Code:

Sub PrintInvoices()

Dim strPrintArea As String

    strPrintArea = Range("rngPrint").Value
    Worksheets("Report").PageSetup.PrintArea = strPrintArea
    Worksheets("Report").PrintOut Copies:=1, Collate:=True, ActivePrinter:= _
        "Auto HP LaserJet 4000 Series PCL 6 on PAT on Ne14:", Collate:=True
    
End Sub


This macro has worked just fine for several months, but recently it started printing to my local printer (which is also set as my default printer) for no apparent reason; and I can't get it to change back to the network printer. I have verified the following:
- the network name of the device has not changed
- I still have access to it from my PC (I still see it in my Printers folder)
- The printer is physically working just fine
- I can print to it manually from my PC

I have even re-recorded the macro and the code is exactly the same as it was before (minus the code I added). Has anyone else had this problem? If so, what was your solution? Is there a better (i.e. more reliable) method of printing to a network printer (or any printer other than your default)?

Many thanks in advance for any advice or assistance anyone might be able to offer.


Hi all, this is my first post in the forum
I've this problem:
I use this code to print one excel sheet on a network printer

If macchina = "507" Then
Worksheets(Fogliocorrente).PageSetup.PrintArea = "$A$1:$S$11"
ActiveWindow.SelectedSheets.PrintOut Copies:=2, ActivePrinter:="\\Bimec\TAGLIO2 Zebra TLP2844", Collate:=True
End If

Sometime the the procedure hags because the remote printer spooler is in error or the printer is switch off or has finished the ribbon so all my job queue is stopped and all other users cannot print their labels until the remote printer is restored.
I'd like to know is there is a way to test the remote printer status or insert a timeout in the print job so the proces can go on.

Sorry for my bad english and i hope someone can give me a solution
Best regards
Andrea from Italy


Here is some code that I use to print multiple sheets by pressing a button. I want it to change to a specific printer, called "PDFcreator" in my printer list. What can I add to do that? Thanks

Private Sub CommandButton1_Click()
'PRINT LRFD ATTACHED CALC'S WITH DB-SAE'S
'

'
Sheets("ATT INFO").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("ATT Service").Select
ActiveSheet.PageSetup.PrintArea = "$A$55:$F$137"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("ATT Strength").Select
ActiveSheet.PageSetup.PrintArea = "$A$56:$F$154"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("ATT Extreme").Select
ActiveSheet.PageSetup.PrintArea = "$A$56:$F$154"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("INPUT").Select
End Sub


Code:

 
Application.ActivePrinter = "HP LaserJet on Ne01:"
    Selection.PrintOut Copies:=1, Collate:=True



The issue is that in the macro I often swtich from print to default printer to Print to Adobe PDF

This macro is run on multiple PCs.

Utlimately I just need the code to automatically print to default printer.

when I run the code

Code:

   Application.ActivePrinter = "Adobe PDF on Ne02:"


It changes the printer.

Since this is run on multiple computers with different printer settings it would be much easier if it was set to default printer then adobe pdf and back to default printer


I have this code:
Code:

 
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    
    If ActiveSheet.Name = "Address" Or ActiveSheet.Name = "Labels 6" Then
            Application.ActivePrinter = "\\Sidonna\hp deskjet 3500 series on Ne07:"
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
'            Application.ActivePrinter = "\\Sidonna\EPSON Stylus C86 Series on Ne03:"
Else
    PrintLabsYesNo = MsgBox("Do you want to print?" & vbCrLf & vbCrLf _
                      & "The current active printer is: " & Application.ActivePrinter, vbYesNo, "Print lables")
    Select Case PrintPrevYesNo
        Case Is = vbYes
            Application.ActivePrinter = "\\Sidonna\HP DeskJet 895Cse on Ne05:"
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
            Application.ActivePrinter = "\\Sidonna\EPSON Stylus C86 Series on Ne03:"
        Case Is = vbNo
    End Select
End If
End Sub


If it is on one of the 2 worksheets "Address" or "Label 6" It prints on the correct printer, then it displays the Print Dialog box??
Also, after it does print, I need it to set the default printer back to the Epson Stylus printer.
You can see the line dimmed out where I tried to set the printer to the stylus, if I undim it it prints on both printers????
Thank you,
Michael


I currently have a spreadsheet that I run a macro on to save the sheet and print 2 copies out. This works OK.

In some cases I now need to output as a PDF and print 1 copy.

I have found the relevant code for swapping the active printer to my PDF printer and back, however, what I would like is one print button that knows whether I require 2 printed copies or 1 PDF+1 print.

I have put a vlookup on my sheet to find and show this setting in cell L2; so basically I want the macro to run

IF L2 <> PDF then print 2 copies ELSE print 1 to pdfprinter and 1 to printer.

How do I build this IF statement into my current code?

Thanks


Alright, I have another one. I have a code that prints out sheets for me in a
certain order. I have the code open up a word document and then prints that
document. However, it will not print to the color printer, it only prints to
my default printer. How can I make the code have the word doc print to my
color printer. Here is my current code. Any help is appreciated. Thanks.

On Error Resume Next
Application.ActivePrinter = "HP C LaserJet 4550N PCL6 on Ne01:"
If Err.Number = 1004 Then
Application.ActivePrinter = "hp c Laserjet 4550N PCL6 on Ne02:"
Err.Clear
End If
Application.ScreenUpdating = False
Sheets("Letter").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Cover").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Agreement").Select
Dim WD As Object
Set WD = CreateObject("Word.Application")
WD.Documents.Open ("G:\CONTRACT\Contract Terms\macro\2005
t&cscontract.doc")
WD.ActiveDocument.PrintOut Background:=False
WD.Application.Quit SaveChanges:=wdDoNotSaveChanges
Set WD = Nothing
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Agreement").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Cover").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Agreement").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.ScreenUpdating = True



Hi All
Apologies for another rubbish/simple excel query....

I have a spreadsheet of many worksheets, on each worksheet there are two macro buttons; one to "Print to Printer" and another to "Print to PDF".

On the network there are only two printers, and only two PDF makers....instead of having four buttons (one for each outcomes), can I write a macro to, for example;
Print to Printer:
Attempt to printer to 'C:/...Printer1'
if fail
Attempt to print to 'C:/....Printer2'

Any help would be much appreciated.

Just a footnote, I realise you can 'print to active printer' - but we need the option to printer to either PDF or Printer, which, i think eliminates this option...

Thanks all.


I am using the following VBA macro to print out an Excel document. Using this I get page 4 twice. I would like to start printing this to Microsoft Office Document Image Writer, instead of a printer, and saving the file so I can email it. This macro gives me 2 documents, however. Rather than add another page 4 to the Excel sheet, which would be hard because of the complicated footer and the need for both page 4 footers to say "Page 4 of 6," is there a way to modify the code so it prints into one .mdi file?

Also, to print to Image Writer, I am making it the default printer before I print. If I could do this, and undo it in the code, that would be great as well. Thanks!

Sub PrintIllustration()

Sheets("Illustration").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=4, Copies:=1, Collate _
:=True
ActiveWindow.SelectedSheets.PrintOut From:=4, To:=6, Copies:=1, Collate _
:=True
Sheets("Entry").Select

End Sub


I checked my USB Port and all connections but I can't get my printer to print
- I don't get an error message and when I go to control panel it shows that
it is set as printer but doesn't show any documents in print que. When I hit
the print icon it shows my Epson C86 printer and gives two messages that it
is printing on my printer but nothing happens. I have turned the printer on
and off and reconnected the wiring. Help me please!



I have a command button that prints the active worksheet to the default printer using the following code:
Code:

Private Sub CommandButton8_Click()
Sheets("Sheet1").PrintOut
End Sub


Is there a way to specify a different printer in the code so the page prints to THAT printer instead of the default printer? I do not want a dropdown that allows the user to choose from a list. Thank you in advance for your help.


I was about to use this code to give the user the option of printing or creating a PDF with tick box.

If (PDF.Value = "True") Then
Application.ActivePrinter = "Adobe PDF on Ne02:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="Adobe PDF on Ne02:", Collate:=True
Else
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If

I obtained the create PDF from recording a macro.
However once I use the create PDF the active printer stays on PDF.

I could use this
Application.ActivePrinter = "Canon i6500 on Ne00:"
To set the printer back.

However what do you do when you don't know who's computer it is going to be run on.

Is there a way to create the PDF without changing the default printer ?


EDIT:
Sometime I need to print the same page over and over, can I send in to one PDF file instead of being prompted to save it for each page ?


Im using the following code to print a worksheet:

Code:

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True


However, I do not want the print process to run all the way through, I'd rather the Print window appeared so the user can select desired printer and other options.

Would someone be able to point me in the right direction please?


I have a button to print selected sheets. It works great...however, it automatically prints to the default printer. (which is not color, and its needs to be).

How can I hit the button, and then have excel show the typical select printer box.



Here's what I've got

Sub PrintOutput()
Application.ScreenUpdating = False
Sheets("Output").Select
Range("A1:P57").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$P$57"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Inputs").Select
Range("K2").Select

Application.ScreenUpdating = True

End Sub


hi, i'd like a button which if i press would call the print selection screen (typically seen by pressing ctrl+p).

so far i've only found code for direct printing:

ActiveWindow.SelectedSheets.PrintOut


which normally is fine but if i'm away from my default printer it causes runtime error 1004
(missing printer)


I have a command button in a spreadsheet with the following code attached to it

Code:

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="\\as2ps002\TLG HP 1022n"


How can I change the printer back to the original default printer after the button is used to print the sheet? The added complexity to this is that it is a shared file and there are a number of default printer across all users