Macros to retrieve addresses from multiple sheets into one combined sheet

[kleinermuk]

I have a file with combined list of 693 companies as sheet 1.

Then I have all those 693 companies' contact information in the same file, but on separate sheets numbered from sheet 20 to sheet 712.

The problem is that addresses are NOT always in the same fields on every of 692 source sheets.
The good news is that every address will always have ", ON" in it.

I need macros to retrieve addresses one by one, starting from sheet 20. If macros finds any ", ON" on sheet 20, it has to be put that field value to field E1 of combined sheet 1, then it moves to sheet 21 and put result(if any found) to field E2 of the combined sheet 1 etc etc

Also, some sheets might have more then one field containing ", ON" in it. In that case second address field should be moved to column F, but on the same raw. If it finds 3rd addresses on the same sheet it should move it to column G of the same raw etc etc. until all addresses from that sheet are imported, then it moves to the next sheet.

If no addresses are found on any of the sheets, that line is skipped and macros is moving to the next sheet.

 

[kleinermuk]

I used the following macros, but it only retrieves 1 record with ", ON" in it, where most of sheets have more then available.

Can someone point how to tune it so it can retrieve all available addresses?

Sub ImportAddresses()
Dim txt, fn As Variant, i As Long
txt = ", ON"
For i = 20 To 712
Set fn = Sheets(i).UsedRange.Find(txt, , xlValues)
If Not fn Is Nothing Then
Sheets(1).Cells(i - 19, 4) = fn.Value
End If
Set fn = Nothing
Next
End Sub