VBA - Copy values instead of formula

[floggingmolly]

I have workbook with 2 tabs, All Data and Red Flags. I have a code that copies only the rows on the All Data tab that don't already exist on the Red Flags tab. There is a formula in column A, and I would like for to copy the values instead of the formula. Can someone help with my code below? I've tried but can't get it to paste the values for some reason.

Sub CopyRowsToSheet2()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lastRow1 As Long
    Dim lastRow2 As Long
    Dim i As Long
    Dim found As Boolean
    
    ' Set references to the worksheets
    Set ws1 = ThisWorkbook.Sheets("All Data")
    Set ws2 = ThisWorkbook.Sheets("Red Flags")
    
    ' Find the last row in each sheet
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row in Sheet1
    For i = 2 To lastRow1 ' Assuming data starts from row 2
        
        ' Reset the found flag
        found = False
        
        ' Check if the row already exists in Sheet2
        For j = 2 To lastRow2
            If ws1.Cells(i, "A").Value = ws2.Cells(j, "A").Value Then
                found = True
                Exit For
            End If
        Next j
        
        ' If the row doesn't exist, copy it to Sheet2
        If Not found Then
            lastRow2 = lastRow2 + 1 ' Increment last row in Sheet2
            ws1.Rows(i).Copy Destination:=ws2.Rows(lastRow2)
        End If
    Next i
    
    ' Cleanup
    Set ws1 = Nothing
    Set ws2 = Nothing
End Sub

 

[Georgiboy]

You have a couple of options with the first being the preference:

'opton 1
ws2.Rows(lastRow2).Value = ws1.Rows(i).Value

'option 2
ws1.Rows(i).Copy
ws2.Rows(lastRow2).PasteSpecial xlValues

 

[floggingmolly]

You have a couple of options with the first being the preference:

'opton 1
ws2.Rows(lastRow2).Value = ws1.Rows(i).Value

'option 2
ws1.Rows(i).Copy
ws2.Rows(lastRow2).PasteSpecial xlValues

Thank you for the quick response. Option 1 is perfect. I appreciate your help.