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