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>
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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?
 
Upvote 0
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
 
Upvote 0
You're welcome, glad to help, & thanks for the feedback.:)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top