Please help to finalize a code...

[BELFRED6]

Hello, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
In order to print invoices based on the result of a dropdown list, I am using the following code that Jon von Der Heyden helped me build. Thousands of thanks to him!<o></o>
<o> </o>
Sub Print_Invoice()

Dim rCell As Range

With ActiveSheet 'change to relevant sheet name if you want to execute macro from another sheet

For Each rCell In .Range("A2:A10")
.Range("J3").Value = rCell.Value: Calculate
If .Range("P14").Value > 0 Then .PrintOut
Next rCell
End With

End Sub <o></o>
<o> </o>
<!-- / message --><!-- sig -->You can see all details of that previous post at http://www.mrexcel.com/forum/showthread.php?t=376244&highlight=printing <o></o>
<o> </o>
I am trying to include in that code two more parameters:<o></o>
1/ Each invoice needs to be printed TWICE.
2/ I would like to print out invoices in an ALPHABETICAL order provided by the content of cell H3 (name of the deliverer). In H3, I have a vlookup function changing the name of the deliverer based on the selected client from the dropdown list located in J3.
Any idea how to do that?

Your help would be very valuable. <o></o>
<!-- / message -->

 

[shyrath]

for the printout try

Then .PrintOut Copies:=2

For the alphabetical. You´d need to sort your range first, but not sure how the range looks?

 

[BELFRED6]

Thanks but now I don't get it.

I added what you suggested but now the macro no longer works and returns a syntax error.
Sub Print_Invoice() is yellow highlighted
For Each rCell In .Range("A2:A10") appears in red letters.

I am totally confused...

 

[shyrath]

Hi

Did you change it to look like this?
I tried it and it tries to print 2 copies.

Sub Print_Invoice()

Dim rCell As Range

With ActiveSheet 'change to relevant sheet name if you want to execute macro from another sheet

For Each rCell In .Range("A2:A10")
.Range("J3").Value = rCell.Value: Calculate
If .Range("P14").Value > 0 Then .PrintOut Copies:=2
Next rCell
End With

End Sub

 

[BELFRED6]

Thank you so much for your help. I copied and pasted the whole code again and now it did work. That's great.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
So now that my printing problem is solved, how can I set an ALPHABETICAL PRINTING ORDER? I would like to have the macro print out all invoices delivered by Driver AAA first, then all invoices delivered by driver BBB, and so on...
<o></o>
In cell H3 of the invoice form, appears the name of the deliverer. This name changes depending on the client's name returned by the dropdown list located in J3.<o></o>
I hope I am clear enough so you can guide me here. Thanks a lot for your advice!<o></o>
<o> </o>

 

[shyrath]

i´m afraid that will only work with this loop if you´ve managed to sort the invoices to deliverer before the loop starts.

So we´d need to look at how to achieve that, and for that you need to post an example table of how this looks.

 

[BELFRED6]

Before I started using VLOOKUP functions and a dropdown list, I had one worksheet for each store (each worksheet had the invoice form for that specific store).
I then used a code for the alphabetical order printing. It is part of the code below. That code was designed to print out each worksheet if the value of cell J1>0 and it printed out the number of copies mentioned in cell L1. The sorting issue was solved by the last part of the code but it does not mean anything to me…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> Is it possible to use the last part of the code below in the new code provided by Shyrath? </o>
<o> </o>
Sub test()<o></o>
Dim i As Long, a(), n As Long<o></o>
ReDim a(1 To Sheets.Count, 1 To 2)<o></o>
For i = Worksheets("ТОРТЫ").Index + 1 To Worksheets.Count<o></o>
With Worksheets(i)<o></o>
If .Range("J1").Value <> "" Then<o></o>
n = n + 1<o></o>
a(n, 1) = .Name<o></o>
a(n, 2) = .Range("J1").Value<o></o>
End If<o></o>
End With<o></o>
Next<o></o>
VSortMA a, 1, n, 2<o></o>
For i = 1 To n<o></o>
With Sheets(a(i, 1))<o></o>
.printout Copies:=.Range("L1").Value<o></o>
End With<o></o>
Next<o></o>
End Sub<o></o>
<o> </o>
Private Sub VSortMA(ary, LB, UB, ref)<o></o>
Dim M As Variant, i As Long, ii As Long, iii As Long<o></o>
i = UB: ii = LB<o></o>
M = ary(Int((LB + UB) / 2), ref)<o></o>
Do While ii <= i<o></o>
Do While ary(ii, ref) < M<o></o>
ii = ii + 1<o></o>
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace>Loop</st1lace><o></o>
Do While ary(i, ref) > M<o></o>
i = i - 1<o></o>
<st1lace>Loop</st1lace><o></o>
If ii <= i Then<o></o>
For iii = LBound(ary, 2) To UBound(ary, 2)<o></o>
temp = ary(ii, iii): ary(ii, iii) = ary(i, iii): ary(i, iii) = temp<o></o>
Next<o></o>
ii = ii + 1: i = i - 1<o></o>
End If<o></o>
<st1lace>Loop</st1lace><o></o>
If LB < i Then VSortMA ary, LB, i, ref<o></o>
If ii < UB Then VSortMA ary, ii, UB, ref<o></o>
End Sub<o></o>