Copy, Search and paste from two different sheets

[eq52515]

I have the following problem:
I would like to copy a cell in sheet one, switch to sheet two and search for the value that was copied; once found, then move over 8 columns and copy the value of that cell, switch back to sheet one, move over 8 columns and paste that value. And do this for as many items in Column C ( I need to copy info from master sheet to my 'working set' sheet without having to search for each and every serial number).

I have looked at past similar questions but haven't solved it yet.

TIA.

 

[mumps]

Hello TIA. In what column in Sheet2 are you searching for the value from column C in Sheet1?

 

[eq52515]

Column B

 

[mumps]

Try:

Sub CopyCell()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Working Set").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim SerNum As Range
    Dim foundSerNum As Range
    For Each SerNum In Sheets("Working Set").Range("C2:C" & LastRow)
        Set foundSerNum = Sheets("Master").Range("B:B").Find(SerNum, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundSerNum Is Nothing Then
            SerNum.Offset(0, 8) = foundSerNum.Offset(0, 8)
        End If
    Next SerNum
    Application.ScreenUpdating = True
End Sub

Make sure that the sheet names in the code match your actual sheet names.

 

[eq52515]

Thanks mumps, I will be out of the office for a couple of weeks, but i will try it as soon as I get back. Thanks.

 

[eq52515]

Works like a charm mumps, thanks for the assist and teaching me something new.

-Eq

 

[mumps]

My pleasure.