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.

If you only care about values, then it is not necessary to use *either* the `Copy` or `Paste/PasteSpecial` methods. You can write range of values directly to another range, try it with something like: `Sheets(2).Range("A1:B10").Value = Sheets(1).Range("A1:B10").Value` — David Zemens, Aug 04 '14 at 02:41

score 2 · Accepted Answer · answered Aug 03 '14 at 23:59

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

score 1 · Answer 2 · edited May 23 '17 at 12:31

1

Instead of using Paste, you can use PasteSpecial and specify xlPasteValues which will only paste values and will ignore everything else.

However, note that PasteSpecial is a method of the Range object, not the Worksheet.

These SO questions sound very relevant in your case as well:

edited May 23 '17 at 12:31

Community

1
1

answered Aug 03 '14 at 17:52

djikay

10,450
8
41
52

@user3100587: Hard to guess what exactly you need to paste, but if I understood it correct, try replacing: `ActiveSheet.Paste` with: `Sheets("New2").Selection.PasteSpecial xlPasteValues`. – djikay Aug 03 '14 at 18:04
I tried it but still have error. Could you tell me why does my code hard to guess?! Am I missing something? – Mouneer Aug 03 '14 at 18:22
@user3100587: You'll need something like: `Rows(LCopyToRow).EntireRow.PasteSpecial xlPasteValues` but there are other problems with your code. For example, you refer to ranges without specifying which sheet they refer to, so the copy/paste functionality is not clear and clearly won't work. Without knowing what you're trying to achieve, it's hard for me to know how to best give you advice. Just read the questions and answers I copied in my post and debug your application line by line to make sure it does what you expect. – djikay Aug 03 '14 at 19:44

VBA to paste only values instead of paste every thing

2 Answers2