Macro/VBA needed to find, match, copy/paste

December 2016



Issue


Ok so i've searched and searched and still haven't found code to do exactly what I need.
The macro needs to be able to search hundrends of cells in the same column (sheet1) and if a cell in that column matches the first cell in another worksheet (Sheet2), then the macro pastes the following cells (from the same row on sheet2) on sheet1 (same row but different column ).
So i'm basically trying to cut out of the work of matching text from two different worksheets and copying and pasting the related data into the same row.
I found this macro as a standard module (not sure how helpful it is, but could be a starting point).


Option Explicit
Sub CopyData()
Dim cell As Range
Dim rw As Long
For Each cell In Worksheets("PTR").Range("A:A").Cells
If cell <> "" Then
rw = Lookup(cell.Value)
If rw <> 0 Then
Worksheets("PTR").Cells(cell.Row, "L").Resize(, 4).Value = _
Worksheets("Reference").Cells(rw, "L").Resize(, 4).Value
End If
End If
Next
End Sub
Function Lookup(item As String) As Long
On Error Resume Next
Lookup = WorksheetFunction.Match(item, Worksheets("Reference").Range("A:A"),
False)
On Error GoTo 0
End Function

Solution


You can do it without a macro
Put the below formula in B1 in sheet 2


=vlookup($A1,CELLREF-SHEET1,column(),false) 


The syntax is VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Note


Thanks to Excelguru for this tip on the forum.

Related :

This document entitled « Macro/VBA needed to find, match, copy/paste » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.