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