Macros to retrieve addresses from multiple sheets into one combined sheet

kleinermuk

New Member
Joined
Nov 3, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

kleinermuk

New Member
Joined
Nov 3, 2020
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,625
Messages
5,625,947
Members
416,144
Latest member
JohnMacDonald

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top