Search and paste a column from sheet1 to sheet2

Question

I tried to do search, copy and paste to sheet2 from sheet1. First I need to search a value of a column in sheet2 then match the value in sheet1, and copy then paste the entire row to sheet2. For example I have this data below

Sheet2

A   B    C         D    E   F
        1200-24                 
        1201-24                 
        120-24                  
        1218-24

Sheet1

A  B   C           D     E      F   
2 345  1200-24     ABD   1er    3.4     
3 456  1201-24     CDF   de3    6.6     
7 780  120-24      EFg   d45    9.6 
9 657  1218-24     VGA   9ml    8.7

so I want all data from c1 on sheet 2 to be searched on sheet 1, if matched then paste to row 2 sheet 2, c2 on sheet 2 to be searched on sheet 1, if matched then paste to row 3 sheet 2, etc.

I found this code from this site:

Sub SearchForString()

Dim LCopyToRow As Integer


On Error GoTo Err_Execute


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

Dim sheetTarget As String: sheetTarget = "sheet2"
Dim sheetToSearch As String: sheetToSearch = "sheet1"
Dim targetValue As String: targetValue = Sheets(sheetTarget).Range("C2").Value  'Value in sheet2!A1 to be searched in sheet1
Dim columnToSearch As String: columnToSearch = "C"
Dim iniRowToSearch As Integer: iniRowToSearch = 2
Dim LSearchRow As Long 'As far as it is not clear the number of rows you will be considering, better relying on the long type
Dim maxRowToSearch As Long: maxRowToSearch = 2000 'There are lots of rows, so better setting a max. limit

If (Not IsEmpty(targetValue)) Then
    For LSearchRow = iniRowToSearch To Sheets(sheetToSearch).Rows.Count

        'If value in the current row (in columnToSearch in sheetToSearch) equals targetValue, copy entire row to LCopyToRow in sheetTarget
        If Sheets(sheetToSearch).Range(columnToSearch & CStr(LSearchRow)).Value = targetValue Then

            'Select row in Sheet1 to copy
            Sheets(sheetToSearch).Rows(LSearchRow).Copy

            'Paste row into Sheet2 in next row
            Sheets(sheetTarget).Rows(LCopyToRow).PasteSpecial Paste:=xlPasteValues

            'Move counter to next row
            LCopyToRow = LCopyToRow + 1
        End If

        If (LSearchRow >= maxRowToSearch) Then
            Exit For
        End If

    Next LSearchRow

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

    MsgBox "All matching data has been copied."
End If

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub

but it won't let me move from c2 to c3 on sheet2, and also the code LCopyToRow = LCopyToRow + 1, not moving to the next row on sheet2. Your help much appreciated. thank you

EDITED: I change the line Dim targetValue As String: targetValue = Sheets(sheetTarget).Range("C2").Value ---- >> it was range ("A2"). thank you

Did you write that? or did you record a macro? For what you describe, I would start with the macro recorder .. see what it spits out, then tweak it a bit as needed. Try the macro recorder: start recording, do your search/copy/paste then stop it and look at the code. — Ditto
I got this code by googling it, and I didn't use macro, but instead I run it with F8 and debug the code by line, and it showing that the value only specified for Column C2, but it doesn't move to C3, and if change target value to C3 manually, the lcopytorow won't move to next row. thank you for your reply — icxb

rgo rgo · Accepted Answer · 2015-04-10T20:31:46

If what you are looking to do is copy all the cells from one row to another sheet, learn to use the match and index functions.

The match function will return the row number that matches what you are searching for. Put the match function in one column to locate the row. Then use the index formula to copy the data from the row. Repeat the index function for each column desired using the match row column value as the row argument.

This is very efficient in that you are searching for each value once but copying the data via index many times. It is also faster than using nested loops to do the find.

After you have set up the formulas to link and pull the data, you can use the macro recorder to store the formulas in code so you can copy them down the spreadsheet, from top to bottom to transfer the values. Afterwards, you can copy paste values the whole worksheet to be left with the data only.

You can also keep the formulas in row two only to use the worksheet as a template for future copies. Then you simply paste in the search value column and copy down the formulas. And did I mention that you can arrange the columns in a different order than the source worksheet, as well as sort the new worksheet at any time and have the rows properly linked to the source data.

Search and paste a column from sheet1 to sheet2

1 Answers