Hello all, I am trying to copy rows from one sheet in a workbook to another sheet based on value in a field. Everything works fine except when the copy (paste special) function triggers it is only copying 1 row. If there are multiple rows that meet the criteria, how do I get it to copy more than one row?
Here is the code I am using:
ActiveSheet.Name = "Not Retained" 'Go to Full List to start searching Sheets("Full List").Select 'Start search in row 2 LSearchRow = 2 'Start copying data to row 2 in Not Retained (row counter variable) LCopyToRow = 2 While Len(Range("A" & CStr(LSearchRow)).Value) > 0 'If value in column D = "N", copy rows A:H to Not Retained to row 2 If Range("D" & CStr(LSearchRow)).Value = "N" Then 'Select row in Full List to copy Range(Cells(LSearchRow, "A"), Cells(LSearchRow, "H")).Copy 'Paste row into Dashboard in next row Worksheets("Not Retained").Range("A2").PasteSpecial Paste:=xlPasteValues 'Move counter to next row LCopyToRow = LCopyToRow + 1 'Go back to Full List to continue searching Sheets("Full List").Select End If LSearchRow = LSearchRow + 1 Wend
Any help that you can give would be appreciated!