Hi everyone, I'm currently trying to copy and paste rows from one sheet to two others, with the destination sheet being dependent on the value in a certain cell in each row. For example,
<tbody>
</tbody>When I run my copy/paste macro, the rows ending in "a" are pasted into sheet2, and the rows ending in "b" are pasted into sheet3. However, they are pasted into the original lines, rather than starting again from the top, as seen below.
<tbody>
</tbody>Is there a way to send all of the pasted lines to the top of the destination sheet so there aren't any blank rows between them? Below is the code I have now. Thanks!
x | x | x | a |
y | y | y | b |
x | x | x | a |
y | y | y | b |
<tbody>
</tbody>
y | y | y | b |
y | y | y | b |
<tbody>
</tbody>
Code:
Sub EGS_CVS_Sorting()Dim lr As Long, lr2 As Long, r As Long
lr = Sheets("template").Cells(Rows.Count, "L").End(xlUp).Row
lr2 = Sheets("EGS lines").Cells(Rows.Count, "L").End(xlUp).Row
lr3 = Sheets("CVS lines").Cells(Rows.Count, "L").End(xlUp).Row
For r = lr To 2 Step -1
Select Case Range("L" & r).Value
Case Is = "1a"
Rows(r).Copy Destination:=Sheets("EGS lines").Range("A" & lr2 + 1)
lr2 = Sheets("EGS lines").Cells(Rows.Count, "L").End(xlUp).Row
Case Is = "1b"
Rows(r).Copy Destination:=Sheets("CVS lines").Range("A" & lr2 + 1)
lr2 = Sheets("CVS lines").Cells(Rows.Count, "L").End(xlUp).Row
End Select
Next r
End Sub