Macro to find list in other spreadsheet and returns the entire row and table's title

[paulaks]

I need help in developing a macro that takes each row from a column in one spreadsheet, searchs for the same string in other spreadsheet and returns the row in a third new spreadsheet. Besides the entire row, the macro must look for the imediate first string formatted in bold above the index it found and put it in a row along with the one it copied. For example:
Spreadsheet one:

A
B
C
D
E

Spreadsheet two:

Canada
C 40,0 3,0 89
B 34,0 2,0 94

Australia
A 50,0 1,0 89
B 33,0 1,0 90

USA
A 60,0 2,0 94
C 54,0 3,0 91
E 67,0 1,0 87




The macro will return:

A Australia 50,0 1,0 89
USA 60,0 2,0 94
B Canada 34,0 2,0 94
Australia 33,0 1,0 90
C Canada 40,0 3,0 89
USA 54,0 3,0 91
E USA 67,0 1,0 87

Thank you for all the help!

 

[paulaks]

For now, I'm using this code I found online but I need to add an extra column after every letter (A, B, C...) indicating the country. I'm thinking about everytime it finds a letter it runs to the top of the table to find the next text written in bold. That way the final sheet would be:

A Australia 50,0 1,0 89
A USA 60,0 2,0 94
B Canada 34,0 2,0 94
B Australia 33,0 1,0 90
C Canada 40,0 3,0 89
C USA 54,0 3,0 91
E USA 67,0 1,0 87

which is great.
Any thoughts?



Code:
Sub gameHunter()
'Determine last row with data in Sheet 2 Column B
lastSrc_rw = Sheets(2).Range("B" & Rows.Count).End(xlUp).Row
'Loop through list of game
For Each game In Sheets(2).Range("B1:B" & lastSrc_rw)
'Search for each game, copy row if found
With Sheets(1).Columns(2)
Set c = .Find(game, LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
'Find next open row in Sheet 3
nxtDst_rw = Sheets(3).Range("B" & Rows.Count).End(xlUp).Row + 1
'Copy row containing found game
c.EntireRow.Copy Destination:=Sheets(3).Range("A" & nxtDst_rw)
'Search for same game again, stop when no more found
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next
End Sub