VBA to paste only values instead of paste every thing

Question

I am working on a small project which requires me to search for a word through one column then copy the entire row that contains my word into another sheet. This is my code and what I have so far:

    Sub SearchForString2()

    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer

    On Error GoTo Err_Execute

    'Start search in row 4
    LSearchRow = 10

    'Start copying data to row 2 in Sheet2 (row counter variable)
    LCopyToRow = 3

    While Len(Range("J" & CStr(LSearchRow)).Value) > 0

        'If value in column E = "Mail Box", copy entire row to Sheet2
        If Range("M" & CStr(LSearchRow)).Value = "NEW" Then

            'Select row in Sheet1 to copy
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
            Selection.Copy

            'Paste row into Sheet2 in next row
            Sheets("New2").Select
            Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
            ActiveSheet.Paste

            'Move counter to next row
            LCopyToRow = LCopyToRow + 1

            'Go back to Sheet1 to continue searching
            Sheets("Old").Select

        End If

        LSearchRow = LSearchRow + 1

    Wend

    'Position on cell A3
    Application.CutCopyMode = False
    Range("A3").Select

    MsgBox "OK!"

    Exit Sub

Err_Execute:
    MsgBox "An error occurred."

End Sub

I want code to paste only values of the selected row with no formatting or formulas. Any suggestions? Thanks in advance.

Answer 1

Try this:

While Len(Range("J" & CStr(LSearchRow)).Value) > 0

    'If value in column E = "Mail Box", copy entire row to Sheet2
    If Range("M" & CStr(LSearchRow)).Value = "NEW" Then

        Sheets("New2").Rows(LCopyToRow).Value = Rows(LSearchRow).Value

        'Move counter to next row
        LCopyToRow = LCopyToRow + 1

    End If

    LSearchRow = LSearchRow + 1
Wend