Selecting data for Invoice

[Yorksboy]

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:

Please Login or Register  to view this content.

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

[rylo]

Hi

Change the selection lines to be

Please Login or Register  to view this content.

I'd also change your filter range to be

Please Login or Register  to view this content.

so you don't run into any limits on the data.

HTH

rylo

[watersev]

hi Yorksboy, a couple of comments:
1. Do not select object you are working with
2. What is the purpose of sorting if the code uses autofilter that will process unsorted data without problems?
3. You have a fixed range (D2:D16) in your code and it causes missing rows. All the code above can be changed with:

Please Login or Register  to view this content.

Please check attachment, it's an option to make it. Save attachment, open file, select any invoice number in Column A and press "ALT+RIGHT". All rows with activecell invoice number will be copied. If data is not present on Invoice sheet, run code "Enable Events", save changes, close and open file again.

[Yorksboy]

Thanks both.

I've tried both solutions, which solve my problem.

I'm a bit of a novice with regards to programming Excel. The reason that I was sorting the data, was because I was using VLOOKUP which I believe needs the data to be in ascending order?

To finish this element off, how easy would it be to 'write back' a flag to mark which order references have been invoiced?

[rylo]

Hi

How about

Please Login or Register  to view this content.

rylo

[Yorksboy]

Hi

How about

Please Login or Register  to view this content.

rylo

Thanks rylo. All sorted now!