Read a cell from column B, Find it in Column A, Write that row out to a new sheet:

[humerick]

I need to be able to read from a column on one sheet(A), find that data on another sheet(B), write that row out to sheet(A) that matched the search. I know this might sound confusing, so I will try to illustrate it below.

You will always be reading from Col B on Sheet A, finding the match in Row A on Sheet B, writing the row out back on Sheet A, then reading Col B on Sheet A from the new row that you just wrote.

(Beginning)
Sheet A

	A	B
1		Cat
2
3
4

<tbody>
</tbody>

Sheet B

	A	B
1	Cat	Dog
2	Rat	Mouse
3	Dog	Frog
4	Mouse	Cat
5	Frog	Rat

<tbody>
</tbody>

Final
Sheet A

	A	B
1		Cat
2	Cat	Dog
3	Dog	Frog
4	Frog	Rat
5	Rat	Mouse
6	Mouse	Cat

<tbody>
</tbody>

The final pattern that I should end up with on Sheet A is: Whatever is in Column B, then Column A should start with on the next row. I hope this makes sense!
Thank you very much in advance in any help that you can give me on this.
Note: I could start out with Sheet A being blank if I could somehow feed/start the whole process out by getting the first search term (Cat in this example) from somewhere (input)

 

[Fluff]

How about

Sub chk()

   Dim Fnd As Range
   Dim Wrd As String
   Dim Ws As Worksheet
   
   Set Ws = Sheets("[COLOR=#ff0000]Sheet3[/COLOR]")
   With Sheets("[COLOR=#ff0000]Ranking[/COLOR]")
      Wrd = .Range("B" & Rows.Count).End(xlUp).Value
      Do
         Set Fnd = Ws.Columns(1).Find(Wrd, , , xlWhole, , , True, , False)
         If Fnd Is Nothing Then Exit Sub
         .Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 2).Value = Fnd.Resize(, 2).Value
         Wrd = .Range("B" & Rows.Count).End(xlUp).Value
      Loop Until Wrd = .Range("B1").Value
   End With
End Sub

Change sheet names in red to suit.

 

[humerick]

Perfect! It worked like a charm. Thank you so very much! I never would have figured that out.

 

[Fluff]

Glad to help & thanks for the feedback