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]

 

[tfmeier]

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
    with Sheets("As-Of-Trades").Range("A" & lr2 + 1)
        .value=.value
    end with  
End If
Next r
End Sub

Thanks for getting back @Michael M does the code work for you? It's giving me the same result.

Allow me to elaborate a bit futher. Looking at the attached screenshot I have sheet 'src'. This contains raw data (say a data download from a system). In 'All Trades' I enrich the data with other fields and then records with 'YES' values in column E then get copied to sheet 'As-of-Trades' as values (not formula).

It's almost like we need some PasteSpecial xlPasteValues function but I don't understand VBA enough to achieve this.

 

[Michael M]

Try changing to

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)
       With Sheets("As-Of-Trades").Rows(r2 + 1)
        .Value = .Value
    End With
        lr2 = Sheets("As-Of-Trades").Cells(Rows.Count, "A").End(xlUp).Row
End If
Next r
End Sub

 

[tfmeier]

I found some alternative code that I could successfully adopt. All good now.

Public Sub CopyRows()
    Application.ScreenUpdating = False
    Worksheets("Sheet1").Range("A2:H200").Clear
    Sheets("Consol").Select
    ' Find the last row of data
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row
    For x = 2 To FinalRow
        ' Decide if to copy based on column I
        ThisValue = Cells(x, 9).Value
        
        If ThisValue = "ToCorecon" Then
            Cells(x, 1).Resize(1, 8).Copy
            Sheets("Sheet1").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            
            ' Paste values only
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            '
            
            Sheets("Consol").Select
          
        End If
    Next x
    
    Application.ScreenUpdating = True
End Sub

 

[Michael M]

Ok....glad you got a solution !!!...