How to print out at once results of a dropdown list


Board Regular
Oct 31, 2008
Let’s try to be as simple as possible. I have an order list in cells A1:G10 where all my clients are listed as well as what they want to order for the next day. Close to that order list, I built an invoice form in cells H1:P15 with various VLOOKUP functions getting data from the order list. The invoice form contains, in cell J3, a dropdown list of my clients (the source of that list is A2:A10).
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
I would like to run a macro that would print out invoices taking the two following parameters into account: 1) the macro needs to go through ALL the clients from the dropdown list and 2) print out ONLY invoices for clients that placed an order (not all clients order every day). That second condition is fulfilled if cell P14>0 (in P14 you have the total amount due by a client).
Having no knowledge of macro programming, I don’t know how to handle that problem. Thank you very much for your help and advice on that matter.

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

This should get you started. Go to the VBE (ALT+F11) > Insert > Module and paste the code below into the code pane. Close the VBE and to run it hit ALT+ F8 and choose 'Print_Invoice' and hit Run.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Print_Invoice()<br><br><SPAN style="color:#00007F">Dim</SPAN> rCell <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">With</SPAN> ActiveSheet <SPAN style="color:#007F00">'change to relevant sheet name if you want to execute macro from another sheet</SPAN><br>    .PageSetup.PrintArea = .Range("H1:P15")<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rCell <SPAN style="color:#00007F">In</SPAN> .Range("A2:A10")<br>        .Range("J3").Value = rCell.Value: Calculate<br>            <SPAN style="color:#00007F">If</SPAN> .Range("P14").Value > 0 <SPAN style="color:#00007F">Then</SPAN> .PrintOut<br>    <SPAN style="color:#00007F">Next</SPAN> rCell<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
Upvote 0
Thanks Jon.

I did what you suggested but I got the following message error:
Unable to set the PrintArea property of the PageSetup class.

To me, that sentence is not in English... Any suggestion to move forward?

Upvote 0

Take out this line:
.PageSetup.PrintArea = .Range("H1:P15")

but make sure that your sheet is set-up for print already (go through all desired page set-up options).
Upvote 0
ok Jon.

I removed the line you mentioned. Now the macro partially works: I don't get any error message. I see that the macro goes through all names of the dropdown list but the macro does not print out.

I went through all Page setup options I needed, set the Print Area manually (H1:P15). And my printer is on...

The problem is no longer in the macro but elsewhere&
Upvote 0

sorry. I had a wrong reference, that's why I did not print out. It does print out now. Thanks a lot!

Now I have two more problems regarding the printing of my invoices.
1/ Each invoice needs to be printed twice.
2/ I would like to print out invoices in alphabetical order, based on the content of cell H3 (name of the deliverer). In H3, I have also a vlookup function changing the name of the deliverer based on the selected client form the dropdown list.
Any idea how to do that?

Your help is very valuable. Sorry for my ignorance.
Upvote 0

Forum statistics

Latest member
Rich Cohen

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