Copy/Paste destination formatting

ptjimmons

New Member
Joined
Nov 28, 2016
Messages
5
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,
xxxa
yyyb
xxxa
yyyb

<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.
yyyb
yyyb

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

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Try this.
Code:
Option Explicit

Sub EGS_CVS_Sorting()
Dim lr As Long, lr2 As Long, r As Long

    lr = Sheets("template").Cells(Rows.Count, "L").End(xlUp).Row
    
    For r = lr To 2 Step -1
    
        Select Case Sheets("template").Range("L" & r).Value
            Case Is = "1a"
                lr2 = Sheets("EGS lines").Cells(Rows.Count, "L").End(xlUp).Row
                Sheets("template").Rows(r).Copy Destination:=Sheets("EGS lines").Range("A" & lr2 + 1)

            Case Is = "1b"
                lr2 = Sheets("CVS lines").Cells(Rows.Count, "L").End(xlUp).Row
                Sheets("template").Rows(r).Copy Destination:=Sheets("CVS lines").Range("A" & lr2 + 1)
        End Select
        
    Next r

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top