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

April 2018


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
End Sub
Function Lookup(item As String) As Long
On Error Resume Next
Lookup = WorksheetFunction.Match(item, Worksheets("Reference").Range("A:A"),
On Error GoTo 0
End Function


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


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


Thanks to Excelguru for this tip on the forum.
Published by aakai1056. Latest update on December 26, 2009 at 06:12 AM by aakai1056.
This document, titled "Macro/VBA needed to find, match, copy/paste," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (
Excel - How to merge several rows into a single one
Excel - Find occurences of same value in a column