Quote Originally Posted by ElvisFan
If this is too basic or too rough please let me know. I am self taught, so I don't always know the best ways.
Hi ElvisFan,
Welcome to VBAX.
Nice to see you jumping in with a solution, and it's a fair attempt.
Regards MD


A few comments.
The OP mention Worksheets. It's fair to assume that they are in the same workbook.
Variant should be used for arrays etc. and truly variant items. If you know the type, this should be used.
Keep ranges dynamic where possible; it keeps the code flexible. My range will find the last used cell in Column 1
Learn how to use Find; it's much quicker than checking each element in a column of say 30,000 cells. Once you know it, you'll use it all the time.
No need to PasteSpecial here.

[vba]Sub CopyData2()
Dim cel As Range, c As Range
'Sheet2 has the source data. The number you want to copy is in the next column over. column B
'Search in Sheet2 for each cell value from sheet1
For Each cel In Range(Sheets(1).Cells(2, 1), Sheets(1).Cells(Rows.Count, 1).End(xlUp))
'Looks in Sheet2 for coresponding number
Set c = Sheets(2).Columns(1).Find(what:=cel.Value, lookat:=xlWhole)
If Not c Is Nothing Then c.Offset(0, 1).Copy cel.Offset(0, 1)
Next cel
End Sub

[/vba]