Help fix my print macro

twarden

Board Regular
Joined
Jun 29, 2006
Messages
64
I'm close - but I want the 'user' to be able to enter the number zero to either of my prompts without causing the macro to fail. Currently, if a user does not want to print any color copies (i.e. enters 0 - or B&W for that matter) the macro fails. Any ideas?

Code:
Sub Print_Report()
Dim CCopies, bwcopies As Integer

Copies = Application.InputBox("How many color copies?", Type:=1)
Copies = Application.InputBox("How many B&W copies?", Type:=1)
Sheets("YTD").PrintOut Copies:=CCopies, ActivePrinter:= _
        "network_address_goes_here:", Collate:=True
Sheets("YTD Graph").PrintOut Copies:=CCopies, ActivePrinter:= _
        "network_address_goes_here", Collate:=True
Sheets("YTD").PrintOut Copies:=bwcopies, ActivePrinter:= _
        "network_address_goes_here", Collate:=True
Sheets("YTD Graph").PrintOut Copies:=bwcopies, ActivePrinter:= _
        "network_address_goes_here", Collate:=True

End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Try

Code:
Sub Print_Report()
Dim CCopies As Integer, BWcopies As Integer

CCopies = Application.InputBox("How many color copies?", Type:=1)
BWcopies = Application.InputBox("How many B&W copies?", Type:=1)
If CCopies > 0 Then
    Sheets("YTD").PrintOut Copies:=CCopies, ActivePrinter:= _
        "network_address_goes_here:", Collate:=True
    Sheets("YTD Graph").PrintOut Copies:=CCopies, ActivePrinter:= _
        "network_address_goes_here", Collate:=True
End If
If BWcopies > 0 Then
    Sheets("YTD").PrintOut Copies:=BWcopies, ActivePrinter:= _
        "network_address_goes_here", Collate:=True
    Sheets("YTD Graph").PrintOut Copies:=BWcopies, ActivePrinter:= _
        "network_address_goes_here", Collate:=True
End If
End Sub
 
Upvote 0
Check you variable for number of copies
B&W copies is overwriting color copies because the variable is the same "Copies"
 
Upvote 0
Sub Print_Report()
Dim CCopies&, BWCopies&

CCopies = Application.InputBox("How many color copies?" & vbLf & vbLf & _
"From: 0 to as many as you want!", Type:=1)

If CCopies > 0 Then
Sheets("YTD").PrintOut Copies:=CCopies, ActivePrinter:= _
"network_address_goes_here:", Collate:=True
Sheets("YTD Graph").PrintOut Copies:=CCopies, ActivePrinter:= _
"network_address_goes_here", Collate:=True
End If

BWCopies = Application.InputBox("How many B&W copies?" & vbLf & vbLf & _
"From: 0 to as many as you want!", Type:=1)

If BWCopies > 0 Then
Sheets("YTD").PrintOut Copies:=BWCopies, ActivePrinter:= _
"network_address_goes_here", Collate:=True
Sheets("YTD Graph").PrintOut Copies:=BWCopies, ActivePrinter:= _
"network_address_goes_here", Collate:=True
End If
End Sub


This version should switch to the different printers as needed!
 
Upvote 0
Printer macro still not working

Something still isn't quite right. Despite specifying different network addresses (and they are the correct ones), both the "color" & "b&w" copies end up printing out on the same machine. However, if I enter "0" for either B&W or Color, then the reports will print on the correct printer (one is B&W only, the other is defaulted to Color). Any ideas on what is wrong?

Code:
Sub Print_Report()
Dim CCopies As Integer, BWcopies As Integer

CCopies = Application.InputBox("How many color copies?", Type:=1)
BWcopies = Application.InputBox("How many B&W copies?", Type:=1)
If CCopies > 0 Then
    Sheets("YTD").PrintOut from:=1, to:=1, Copies:=CCopies, ActivePrinter:= _
        "Color_printer_network_Address", Collate:=True
    Sheets("YTD Graph").PrintOut from:=1, to:=1, Copies:=CCopies, ActivePrinter:= _
        "Color_printer_network_Address", Collate:=True
End If
If BWcopies > 0 Then
    Sheets("YTD").PrintOut from:=1, to:=1, Copies:=BWcopies, ActivePrinter:= _
        "B&W_printer_network_Address", Collate:=True
    Sheets("YTD Graph").PrintOut from:=1, to:=1, Copies:=BWcopies, ActivePrinter:= _
        "B&W_printer_network_Address", Collate:=True
End If
End Sub
 
Upvote 0
Try my version, I did it in parts so, it should work, see the revised code above!
 
Upvote 0
unfortunately - despite specifying different (and the correct) printer addresses, both the B&W & Color copies both end up printing out on the same (the color one) printer. Any other thoughts?
 
Upvote 0
Are you 100% sure you have the correct names for the printers?

I don't see any network paths or anything like that.:)
 
Upvote 0
I pulled the network names out because they were long & ugly - but yes, the network names are correct.

For whatever reason - if I enter "0" for the color copies, then the B&W copies print out on the B&W printer as they should. However, if I enter a number for color copies, then all of the copies end up printing on that printer...... so that also suggests I have the printer addresses right - but confuses me as to why the color printer is over-writing the B&W...????
 
Upvote 0
They may well be long and ugly, but they may well be what you need to use.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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