Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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

View Answers     

Similar Excel Tutorials

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 PrintOu ...
Print Specific Pages in Excel
This free Excel macro allows you to print a pre-specified selection of pages from Excel. This means you can print ...
Print Only Specific Parts of a Worksheet in Excel
In Excel you can select parts of a worksheet to print while ignoring all of the other data on the worksheet. This a ...
Scale Data for Printing in Excel - Fit more onto a Printed Page
This tip shows you how to fit more Excel data onto a page for printing. This does not change the size, shape, or lo ...

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


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??


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


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 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


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 ?


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


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


Problems with Printer Ports. I use this code often to change the printer default

Code:

Sub Change_Printer()


    steve = Application.ActivePrinter ,saves default printer name
    
    'ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
        "\\server2003\Production HP 4250 on Ne14:", Collate:=True
    
    'You can remove the printer port on this code and it still works

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
        "\\server2003\Production HP 4250", Collate:=True

    Application.ActivePrinter = steve
    
End Sub


What is interesting is that when you print to a printer you do NOT have to specify the printer port in the code you can delete the... on Ne14

Can someone tell me why

Code:

Application.ActivePrinter = "\\server2003\Production HP 4250"


Does not work

I want to change the default printer without doing a printout any ideas please


Hi. I am new to this forum and new to macros and VB. I am doing a project for work and have a question that hopefully someone can help me with

i have a macro that loads a userform for previewing and printing selected sheets. The macro works fine for previewing sheets but when i choose the print button the marco trys to save the file as a .mdi, i want this to select the default printer on the pc instead, the excel file will be onpened on many different pcs so the printers will be different on each machine, hence why i want a default printer selected

This is the section of code from the form

Code:

Private Sub CB_Print_Click()

    Application.ScreenUpdating = False
    Select_Sheets
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Application.ScreenUpdating = True
    
End Sub







Perhaps someone can help with with what is probably a very simple problem. I've been trawling through the internet to find a solution and all I ever seem to find is a solution for the inverse of my problem.

So here it is...

I have a macro on a Word document that will print the document via the macro (bypassing the print dialogue box). What I WANT to do it to print the document to the user's DEFAULT printer (not their current printer). In the majority of cases the default printer will be the current printer, but there are times when a user has another word document open and has the printer set to another printer (let's call it printer 2). The default printer is printer 1 but because they already have a Word doc open and have the printer 2 selected, when the new document (with the macro for printing) is used, the document prints to printer 2 and not printer 1 (even though printer 1 is the default printer - it's just that at this point in time it's not the current printer).

Can anyone tell me what to put in the VBA script to make sure that when the document prints it prints to printer 1 (the default printer) every time, even if another Word doc is already open that has a different printer selected?

Your help, as always, is greatly appreciated.

Kind regards,

Andre

I have a hp deskjet printer set up on a computer and some excell worksheets will not print on it. It will make a noise like it is trying to feed the paper but nothing comes, I can copy and paste the worksheet to another sheet and it will print it and I can select another printer and it will print it. Any ideas on what might be causing this?


Hello all,
I have a large workbook (131 sheets, actually) and have a print area set on each sheet past Sheet 3. The first three sheets have a TON of random data on them and are significantly larger than subsequent sheets.

I am looking for some VBA code to select all sheets after worksheet 3, and then to print each sheet (while respecting the print areas already set). It also has to bring up a printer select dialog box (the default CTRL+P one is fine, my user won't change the settings...) because he moves between offices (and hence different printers).

Thanks in advance everyone! Much appreciated.

Edit: using this code, but I want to know how to be able to get the print dialog...
Code:

Sub Print()

' Select the first sheet for printing
Worksheets(4).Select
' Select Multiple Sheets (sheet 5 and on...)
For i = 5 To Worksheets.Count
    Worksheets(i).Select False
Next i

ActiveWindow.SelectedSheets.PrintOut Copies:=1

' Back to sheet 1
ActiveWorkbook.Sheets(1).Select

End Sub





Hi All

Can someone tell me the code for opening up the print window so you can choose which printer to print to?????

At the moment I'm using

ActiveSheet.PrintOut Copies:=2, Collate:=True

but it prints to the last printer used which in my case might be in another room!!

P.S. What does the collate:=True mean??????

Thanks all




Is it possible to chose witch printer, and set it to print in color in a VB code?
Now I'm using the following code:
Code:

Sub PrintLists()
Application.Dialogs(xlDialogPrint).Show
Worksheets("L_10_Ettermiddag").PrintOut From:=1, To:=2, Copies:=1
End Sub


But I don't want to see the printer dialog box, I want the settings to be in the code "use printer "name" and print in color.
We have loads of printers. And the one I want to use is default black and white.

// Thomas