VBA invoice generation

[bologne]

Good Afternoon (timezone depending)

I have the attached worksheet, basically a stockbook which can also create invoices.

My questions relate to the "Create Invoice" part of my VBA code / workbook.

1) With my invoices, there may be several entries for a Sale ID, if the customer has purchased more than one item. On my userform "Create Invoice" I have an "amount" field which I wish to display the total amount of the sale.

The current code I have in relation to this is as follows:

Private Sub SaleNumber_AfterUpdate()
Dim jRow As Range
Dim ws As Worksheet

Set jRow = Sheets("Sales").Range("$B:$B").Rows.Find(what:=Me.SaleNumber.Value, LookIn:=xlFormulas, lookat:=xlWhole)
Me.Amount.Value = Application.WorksheetFunction.Sum(ws.Cells(jRow.Row, 6))
End Sub

Ideally I want this to add all occurrences of the sale amount where it matches the Sale ID, thus creating a total for that transaction.

2) My second question is linked in a way. Where there may be more than one item per transaction, in the invoice that I'm creating I want to insert the details of each item into a new line within the specific section of the invoice.

On the attached worksheet you can see the "Invoice" worksheet in question. I currently have no idea how to accomplish this, other than perhaps using the "Next *variable*" in some way. Any advice would be appreciated.

Regards
Ralph

[Richard Buttrey]

Hi,

Personally I'd be inclined to simplify it.
There seems no good reason for using much of the VBA and re-inventing the wheel when the addition of normal VLOOKUP() formula can pick up all the name and address info on the top of the invoice once you've included a Client ID drop down.

Then as far as populating the invoice line item details, are concerned a simple Data Filter Advanced could be used to extract all records for a particular Sale ID.

If there's any danger of there being more line items than will fit within the bounds of the current invoice then one solution would be to have the totals and contact details held as a named range somewhere else. After the line item details have been filtered, have a macro copy the range name back to the bottom of the invoice.

Regards