Copy "one to many" records to another worksheet

[excelated]

This is about pulling data from an invoice to an another worksheet, and maintain a database. Pulling invoice number, date, name( of the company), subtotal, taxes and grand total is straight forward,and discussed extensively on the net. I call this one to one.

Howewer, I would like to record the Items, quantity, unit rate and total ( quantity x unit rate) too. I call this one to many ( as there are many items for a given Invoice number) .

I would be grateful, if any of you could suggest improvements to following code or suggest new to include "one to many" type of datas.

Sheet1 is Invoice & Sheet 2 is Sales. G3 and G4 are Invoice number and date resp. I11 to 13 are subtotal, tax and grand total.

Private Sub FillInvoiceList()
Dim rngInvNumber As Range
Set rngInvNumber = Range("Sales!A2:A1000")
For i = 1 To 1000
With rngInvNumber
If .Cells(i, 1) = "" Then
.Cells(i, 1).Value = Sheet1.Range("G3").Value
.Cells(i, 3).Value = Sheet1.Range("G4").Value
.Cells(i, 4).Value = Sheet1.Range("I11").Value
.Cells(i, 5).Value = Sheet1.Range("I12").Value
.Cells(i, 6).Value = Sheet1.Range("I13").Value
Exit For
End If
End With
Next i
End Sub

Let Item x Quantity x Rate x Amount ( IQRA)be in column F,G,H,I, begin from ROW 6 end ROW 10

-One way is to repeat invoice number and date with every IQRA in that perticular Invoice.

-Also, there should be some way to find out whether Next IQRA exists at all. For eample, there are only two IQRA ) 3,4 and 5 are blank.

-The invoice can accommodate upto 5 IQRA.

Please also suggest links to sites/forums where I can learn more about this.

Thanks and happy new year to all of you in advance!

[MSP77079]

Please Login or Register  to view this content.

[excelated]

Thanks a lot!