Trying to Copy Multiple Rows from One Sheet to Another

orthoman1276 · Jun 20th 2016

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:

Code

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

Display More

Any help that you can give would be appreciated!

Thanks

-Ken

**Trebor76** · Jun 21st 2016

Re: Trying to Copy Multiple Rows from One Sheet to Another

Hi Ken,

You have a variable called LCopyToRow which I assume is to paste the data into the Not Retained worksheet yet it isn't used :confused:

As such see if changing this line of your code...

Code

Worksheets("Not Retained").Range("A2").PasteSpecial Paste:=xlPasteValues

...to this does the job:

Code

Worksheets("Not Retained").Range("A" & lCopyToRow).PasteSpecial Paste:=xlPasteValues

Regards,

Robert

orthoman1276 · Jun 21st 2016

Re: Trying to Copy Multiple Rows from One Sheet to Another

Robert thank you so much for your assistance in this. Completely overlooked the lcopytorow...

Works swimmingly!

**Trebor76** · Jun 21st 2016

Re: Trying to Copy Multiple Rows from One Sheet to Another

Thanks for letting is know and you're welcome

Trying to Copy Multiple Rows from One Sheet to Another

Participate now!

Share