Hi You could try this code (see attach document)

Sub copy_test()
Sheets("Sheet1").Select
rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
For i = 2 To rowcount
Sheets("Sheet1").Select
Range("a" & i).Select
first_value = ActiveCell.Value
first_value_mod_1 = Left(first_value, 1)
first_value_mod_2 = Right(first_value, 2)
value_search = first_value_mod_1 & "?" & first_value_mod_2
Range("b" & i).Select
second_value = ActiveCell.Value
Sheets("Sheet2").Select
Cells.Find(What:=value_search, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=
_
False).Activate
first_value_2 = ActiveCell.Value
ActiveCell.Offset(0, 2).Select
third_value = ActiveCell.Value
Sheets("Sheet3").Select
rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
Range("a" & rowcount + 1).Select
ActiveCell = first_value
ActiveCell.Offset(0, 1).Select
ActiveCell = first_value_2
ActiveCell.Offset(0, 1).Select
ActiveCell = second_value
ActiveCell.Offset(0, 1).Select
ActiveCell = third_value
Next
End Sub


+-------------------------------------------------------------------+
|Filename: Book4.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5025 |
+-------------------------------------------------------------------+

--
jetted
------------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
View this thread: http://www.excelforum.com/showthread...hreadid=561064