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:
Where StrInvoiceNo is a user selected number from column A.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
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
Last edited by Yorksboy; 02-10-2012 at 06:31 AM.
Hi
Change the selection lines to be
I'd also change your filter range to beRange("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy Range("E2:G" & Cells(Rows.Count, "D").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
so you don't run into any limits on the data.Range("$A:$G").AutoFilter Field:=1, Criteria1:=strInvoiceNo
HTH
rylo
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 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.With Sheets("Invoice Data") Application.ScreenUpdating = 0 .UsedRange.AutoFilter Field:=1, Criteria1:=strInvoiceNo If .Cells(Rows.Count, "d").End(xlUp).Row > 1 Then .Range("d2", .Cells(Rows.Count, "d").End(xlUp)).Resize(, 4).SpecialCells(xlCellTypeVisible).Copy Sheets("Invoice").Range("A37").End(xlUp).Offset(1).PasteSpecial xlValues End If .UsedRange.AutoFilter Application.CutCopyMode = 0 Application.ScreenUpdating = 1 End With
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?
Hi
How about
ryloRange("J2:J" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Value = "Invoice Done"
Thanks rylo. All sorted now!Originally Posted by rylo
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks