How to print out at once results of a dropdown list

[BELFRED6]

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-comfficeffice" /><o></o>
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).
<o></o>
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.

 

[Jon von der Heyden]

Hello

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>

 

[BELFRED6]

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?

Thanks.

 

[Jon von der Heyden]

Ok

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

 

[BELFRED6]

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&

 

[BELFRED6]

Jon,

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.