VBA to find data in Sheet 2 copy and paste back in Sheet 1

Hello,

I have sheet1 and sheet2.

1) There is list of names in Column A of Sheet1
2) There is data of employee and their prod value in Sheet2. Employee name in Column A and prod value in Column G
3) I need VBA which will pick up the Column A from sheet1, find the match in Sheet 2 and copy the prod value in Sheet 1 Column K.

Please assist.

Thanks,
Shiva

Hi Shiva,

Please see the code below and attachment example as well.
Your feedback will be greatly appreciated!

Sub FindValueOneSheetToAnother()
    Dim i, lastRow As Integer
    Dim productName As String
    
    lastRow = Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        productName = lookupFunction(Sheets("Sheet1").Cells(i, 1).Value, Sheets("Sheet2").Range("A:G"), 7, False)
        Sheets("Sheet1").Cells(i, 11).Value = productName
    Next
End Sub

Function lookupFunction(lookupValue, rnglookupvalue, colIndex, conValue)
    On Error GoTo ErrorHandler
    lookupFunction = Application.WorksheetFunction.VLookup(lookupValue, rnglookupvalue, colIndex, conValue)
    Exit Function
ErrorHandler:
    lookupFunction = "Not Found!!!"
    Exit Function
End Function

I will post this example on my site feel free to see other excel example as well.

Thank you,
Alfonso

Thanks.. Works like a charm..

One question though.. If I want to change the Paste value to column L instead of K..

How to modify your code?

Thanks

np.. got it..

Sheets("Dump").Cells(i, 12).Value = productName

Thanks fonzie29 for your code.

Regards,
Shiva