In a previous post, I was kindly given help on how to select rows on a sheet to copy across to an invoice template. This method worked by using an Autofilter, then copying the data:
Sheets("Invoice Data").Select
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal
Range("$A$1:$G$75").AutoFilter Field:=1, Criteria1:=strInvoiceNo
Range("D2:D16").Copy
Sheets("Invoice").Range("A20").PasteSpecial xlValues
Range("E2:G16").Copy
Sheets("Invoice").Range("B20").PasteSpecial xlValues
Where StrInvoiceNo is a user selected number from column A.
This initially looked OK, but as I've poulated the Invoice Data sheet with more data, the method of selecting & subsequent pasting isn't going to work. It relies on selecting the same number of rows on 'Invoice Data' (D2:D16) that match the table on the Invoice sheet. As the matches may be spread out on the sheet, I need a method which looks down column A, then when a match is found, copies the data over on to the next 'blank' line on the Invoice sheet.
As an examaple, an autofilter on ComOrdRef '2101126', selects the rows 15,16 & 17, so row 17 is missed off the copy & paste function. If I select too large a range, the copying function wipes out part of the invoice template.
Any idea?
Mark
P.S. Apologies for simple sheet & code, as I've had to cut it down to protect the data
Bookmarks