VBA - Copy and paste entire row to second sheet based on cell value

[cwdamron]

Hello,

Today I finally taught myself a basic understanding of macros/VBA. I've made a lot of progress on my project, but am stuck at the moment...Here's what's going on:

I have a sheet titled "All Trades" that contains the raw data like the example below. I have two other sheets titled "As-Of Trades" and "Non As-Of Trades". I'm needing a code to copy the entire row of data from the "All Trades" sheet, and paste it in the next available row on the other two sheets, based on the value of YES or NO.

If Yes - Copy row to sheet titled "As-Of Trades"
If No - Copy row to sheet titled "Non As-Of Trades"

Any help is MUCH appreciated THANK YOU!!

[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD="align: center"]Fund[/TD]
[TD="align: center"]Account[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Gain/Loss[/TD]
[TD="align: center"]As/Of? (Y/N)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]11111[/TD]
[TD]$15000.00[/TD]
[TD]-$1.51[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]22222[/TD]
[TD]$32158.52[/TD]
[TD]$78.14[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123123[/TD]
[TD]$1.00[/TD]
[TD]$0.00[/TD]
[TD]NO[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Michael M]

Try

Sub MM1()
Dim lr As Long, lr2 As Long, r As Long
lr = Sheets("All Trades").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("As-Of-Trades").Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
    If Range("E" & r).Value = "YES" Then
        Rows(r).Copy Destination:=Sheets("As-Of-Trades").Range("A" & lr2 + 1)
        lr2 = Sheets("As-Of-Trades").Cells(Rows.Count, "A").End(xlUp).Row
    End If
Next r
End Sub

 

[cwdamron]

Try

Sub MM1()
Dim lr As Long, lr2 As Long, r As Long
lr = Sheets("All Trades").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("As-Of-Trades").Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
    If Range("E" & r).Value = "YES" Then
        Rows(r).Copy Destination:=Sheets("As-Of-Trades").Range("A" & lr2 + 1)
        lr2 = Sheets("As-Of-Trades").Cells(Rows.Count, "A").End(xlUp).Row
    End If
Next r
End Sub

Thanks Mike! I got made some edits to it and got the first part to work, (column P = YES, move to "As-Of Trades" sheet successfully.) However, I keep getting an error and am not sure how to correct it in order to get the second piece to work, (column P=NO, move to "Non As-Of Trades" sheet. So far this hasn't completed at all as the error is reached before it's to that point).

Below is the code I have thus far:

Sub As_Of_Analysis_Sorting()
Dim lr As Long, lr2 As Long, r As Long
lr = Sheets("All Trades").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("As-Of Trades").Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
    If Range("P" & r).Value = "YES" Then
        Rows(r).Copy Destination:=Sheets("As-Of Trades").Range("A" & lr2 + 1)
        lr2 = Sheets("As-Of Trades").Cells(Rows.Count, "A").End(xlUp).Row
    Next r
Else
    If Range("P" & r).Value = "NO" Then
        Rows(r).Copy Destination:=Sheets("Non As-Of Trades").Range("A" & r + 1)
        r = Sheets("Non As-Of Trades").Cells(Rows.Count, "A").End(x1Up).Row
    End If
    Range("A1").Select
End Sub

 

[Michael M]

Try

Sub As_Of_Analysis_Sorting()
Dim lr As Long, lr2 As Long, r As Long
lr = Sheets("All Trades").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("As-Of Trades").Cells(Rows.Count, "A").End(xlUp).Row
lr3 = Sheets("Non-As-Of Trades").Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
    If Range("P" & r).Value = "YES" Then
        Rows(r).Copy Destination:=Sheets("As-Of Trades").Range("A" & lr2 + 1)
        lr2 = Sheets("As-Of Trades").Cells(Rows.Count, "A").End(xlUp).Row
    End If
    If Range("P" & r).Value = "NO" Then
        Rows(r).Copy Destination:=Sheets("Non-As-Of Trades").Range("A" & lr3 + 1)
        lr3 = Sheets("Non-As-Of Trades").Cells(Rows.Count, "A").End(xlUp).Row
    End If
    Range("A1").Select
Next r
End Sub

 

[cwdamron]

Try

Sub As_Of_Analysis_Sorting()
Dim lr As Long, lr2 As Long, r As Long
lr = Sheets("All Trades").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("As-Of Trades").Cells(Rows.Count, "A").End(xlUp).Row
lr3 = Sheets("Non-As-Of Trades").Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
    If Range("P" & r).Value = "YES" Then
        Rows(r).Copy Destination:=Sheets("As-Of Trades").Range("A" & lr2 + 1)
        lr2 = Sheets("As-Of Trades").Cells(Rows.Count, "A").End(xlUp).Row
    End If
    If Range("P" & r).Value = "NO" Then
        Rows(r).Copy Destination:=Sheets("Non-As-Of Trades").Range("A" & lr3 + 1)
        lr3 = Sheets("Non-As-Of Trades").Cells(Rows.Count, "A").End(xlUp).Row
    End If
    Range("A1").Select
Next r
End Sub

Thanks Mike!! It worked! WOO HOO!!

 

[Michael M]

pleasure...

 

[LauraWork]

Hi all

I have just borrowed this code thanks so much it was just what I was looking for however I think I've gone wrong editing it somewhere. It works by splitting my 3 different data conditions to 3 tabs however then it repeats it's self - how do i tell it to stop?

for example on my test data I had 12 rows with data, 4 of each variable but it just kept duplicating these 4 rows again and again on the new tabs.

Sub Macro1()
'
' Macro1 Macro
'
Dim lr As Long, lr2 As Long, lr3 As Long, lr4 As Long, r As Long
lr = Sheets("raw data").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("23047021 sundries").Cells(Rows.Count, "A").End(xlUp).Row
lr3 = Sheets("23048512 hygiene").Cells(Rows.Count, "A").End(xlUp).Row
lr4 = Sheets("23052521 equip").Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
If Range("A" & r).Value = "23047021" Then
Rows(r).Copy Destination:=Sheets("23047021 sundries").Range("A" & lr2 + 1)
lr2 = Sheets("23047021 sundries").Cells(Rows.Count, "A").End(xlUp).Row
End If
If Range("a" & r).Value = "23048512" Then
Rows(r).Copy Destination:=Sheets("23048512 hygiene").Range("A" & lr3 + 1)
lr3 = Sheets("23048512 hygiene").Cells(Rows.Count, "A").End(xlUp).Row
End If
If Range("a" & r).Value = "23052521" Then
Rows(r).Copy Destination:=Sheets("23052521 equip").Range("A" & lr4 + 1)
lr4 = Sheets("23052521 equip").Cells(Rows.Count, "A").End(xlUp).Row
End If
Range("A1").Select
Next r
End Sub

 

[Michael M]

Hmmm, works fine for me !!
Are your variables ALL numbers ??....or are they actually text ?

 

[Michael M]

This might also be a better option

Sub Macro2()
Dim lr As Long, lr2 As Long, lr3 As Long, lr4 As Long, r As Long
lr = Sheets("raw data").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("23047021 sundries").Cells(Rows.Count, "A").End(xlUp).Row
lr3 = Sheets("23048512 hygiene").Cells(Rows.Count, "A").End(xlUp).Row
lr4 = Sheets("23052521 equip").Cells(Rows.Count, "A").End(xlUp).Row
    For r = lr To 2 Step -1
        Select Case Range("A" & r).Value
            Case Is = "23047021"
                Rows(r).Copy Destination:=Sheets("23047021 sundries").Range("A" & lr2 + 1)
                lr2 = Sheets("23047021 sundries").Cells(Rows.Count, "A").End(xlUp).Row
            Case Is = "23048512"
                Rows(r).Copy Destination:=Sheets("23048512 hygiene").Range("A" & lr3 + 1)
                lr3 = Sheets("23048512 hygiene").Cells(Rows.Count, "A").End(xlUp).Row
            Case Is = "23052521"
                Rows(r).Copy Destination:=Sheets("23052521 equip").Range("A" & lr4 + 1)
                lr4 = Sheets("23052521 equip").Cells(Rows.Count, "A").End(xlUp).Row
        End Select
    Next r
End Sub

 

[LauraWork]

that second one worked great thanks so I'll use that. The numbers are account numbers and saved in number format, I think Excel is playing tricks on me but your amended one works so I am very happy! thanks again!