Search Sheet1 and copy to sheet 2

StuartWB2

New Member
Joined
Nov 13, 2019
Messages
3
I have been tasked with a extracting some data from a spreadsheet not being that good i would like some help with VBA
all the data is on sheet 1 and there is duplicated through out.
the way it is layed out is where ever there is a part number there is a serial number in the right hand cell. What i want to do is use list of part numbers in Colmn A sheet 2 search in sheet 1 and Paste the found serial numbers next to the selected part numbers in sheet 2 if that maked sense.

Thanks

Sheet 1

Part 1Serial
part2serial
Part3Serial
Part 5Serial

<tbody>
</tbody>


Sheet 2
Part 1Serial
Part2
Part 3
Part 4
Part 5

<tbody>
</tbody>
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,241
Office Version
2013
Platform
Windows
Try this:

Code:
Sub a1114933a()
Dim c As Range, f As Range

Application.ScreenUpdating = False
Sheets("Sheet 1").Activate

For Each f In Sheets("Sheet 2").Range("A1", Sheets("Sheet 2").Cells(Rows.Count, "A").End(xlUp))

    Set c = Cells.Find(What:=f.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then f.Offset(, 1).Value = c.Offset(, 1).Value
    
Next

Application.ScreenUpdating = True

End Sub
Note: the sheet's name is "Sheet 1" not "Sheet1", isn't it?
 

StuartWB2

New Member
Joined
Nov 13, 2019
Messages
3
Try this:

Code:
Sub a1114933a()
Dim c As Range, f As Range

Application.ScreenUpdating = False
Sheets("Sheet 1").Activate

For Each f In Sheets("Sheet 2").Range("A1", Sheets("Sheet 2").Cells(Rows.Count, "A").End(xlUp))

    Set c = Cells.Find(What:=f.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then f.Offset(, 1).Value = c.Offset(, 1).Value
    
Next

Application.ScreenUpdating = True

End Sub
Note: the sheet's name is "Sheet 1" not "Sheet1", isn't it?

Works a treat many thanks
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,241
Office Version
2013
Platform
Windows
You're welcome, glad to help, & thanks for the feedback.:)
 

Forum statistics

Threads
1,077,649
Messages
5,335,473
Members
399,017
Latest member
npatel917

Some videos you may like

This Week's Hot Topics

Top