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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

jonesy241

Board Regular
Joined
Dec 16, 2004
Messages
174
Check you variable for number of copies
B&W copies is overwriting color copies because the variable is the same "Copies"
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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!
 

twarden

Board Regular
Joined
Jun 29, 2006
Messages
64
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
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Try my version, I did it in parts so, it should work, see the revised code above!
 

twarden

Board Regular
Joined
Jun 29, 2006
Messages
64
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?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Are you 100% sure you have the correct names for the printers?

I don't see any network paths or anything like that.:)
 

twarden

Board Regular
Joined
Jun 29, 2006
Messages
64
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...????
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
They may well be long and ugly, but they may well be what you need to use.
 

Forum statistics

Threads
1,181,373
Messages
5,929,571
Members
436,679
Latest member
helly123

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