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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Cat[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet B
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cat[/TD]
[TD]Dog[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Rat[/TD]
[TD]Mouse[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Dog[/TD]
[TD]Frog[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Mouse[/TD]
[TD]Cat[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Frog[/TD]
[TD]Rat[/TD]
[/TR]
</tbody>[/TABLE]


Final
Sheet A
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Cat[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Cat[/TD]
[TD]Dog[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Dog[/TD]
[TD]Frog[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Frog[/TD]
[TD]Rat[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Rat[/TD]
[TD]Mouse[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Mouse[/TD]
[TD]Cat[/TD]
[/TR]
</tbody>[/TABLE]



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