Trying to Copy Multiple Rows from One Sheet to Another

  • 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:



    Any help that you can give would be appreciated!


    Thanks


    -Ken

  • 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

  • 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!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!