You're going to need some VB code (a macro) to do this. What needs to happen
in the code is that it starts looking at the sheet with the line item
information and picks up the information in each row in turn, placing that
into the proper locations on the invoice and then printing that sheet. That
would all be done within a loop in the macro.
Now what could make it very easy to code would be if some unique piece of
each invoice's information were in the leftmost column of the table sheet.
Then you could use a VLOOKUP() formula on the invoice sheet for all
information except that one unique item. Your loop would simply pick up the
one unique item and place it into the appropriate location on the invoice
sheet and the VLOOKUP() functions would automatically pickup all the other
data.
Skeleton for such code - you'd need to change things like the names of the
sheets involved and the column/cell references to reflect your reality:
Sub PrintAllInvoices()
Dim lastRow as Long
dim rowPointer as Long
'choose the column with the unique data item in it for this
lastRow = Worksheets("datasheetname").range("A65536").End(xlUp).row
'choose the cell with the first unique item in it
Worksheets("datasheetname").Range("A2").Select
'here the "+ 2" is based on row selected in previous statement
Do Until rowPointer + 2 > lastRow
'move one piece of info from data sheet to invoice sheet
Worksheets("invoicesheet").Range("X9") = _
ActiveCell.Offset(rowPointer,0)
'either do more moves of information using
'variations with the .Offset parameter to choose info from
'other columns, or use VLOOKUP() on the invoice sheet to
'automatically grab related data from the datasheet
'then finally print the invoice
Worksheets("invoicesheet").Printout Copies:=1
rowPointer = rowPointer + 1
Loop
End Sub
