Macros to retrieve phone numbers from multiple sheets on the 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 in separate sheets numbering from sheet 20 to sheet 712.

I need a macros to retrieve phone numbers one by one, starting from sheet 20 and put it to line 1 of combined sheet 1, then retrieve phone number from sheet 21 and put it to line 2 of the combined sheet 1 etc etc

The problem is that phone number are NOT in the same field on each following sheet.

The good news is that phone number is always written in the same format (555) 555-1967

So, macros should search for a number written in that format, once it finds it should import it as a phone number to sheet 1.

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

Phone numbers should be written on the combined Sheet 1 in column D

 

[JLGWhiz]

You can give this a try. It tested OK based on the OP info.

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

 

[kleinermuk]

New macros started to work, but it has to be tuned more.

Currently imported phone numbers go to column A, where they have to be imported to column D
So, phone number from sheet 20 has to be imported into field D1, phone number from sheet 21 has to be imported into field D2...... and so on and so forth until the last sheet, where phone number from sheet 712 has to be imported into field D693.

Right now macros somehow screws results, so I see phone number from Sheet 702 written on line 666, where in fact it has to be placed on line 683. I have a feeling it because of headers, which I have none, but seems like macros thinks otherwise.

 

[JLGWhiz]

I tweaked this a little to put the numbers in column D and changed the starting row to for pasting to row 1. The phone numbers should fall on the row that is 19 less than the sheet index number, or 19 less than the value of the i variable. If no phone number is found then it should just skip that row in the destination sheet. I don't know how the other code managed to paste a 17 row difference for the example you gave because I cannot see your worksheet and have no Idea where the phone numbers are located, if there are more than one phone number per sheet or if the same phone number is referenced in more than one sheet. The code is based solely on information posted in the thread. For better results, post better information.

Here is the modified code.

Sub t2()
Dim txt, fn As Variant, i As Long
txt = "(???) ???-????"
    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

 

[kleinermuk]

for some reason macros missed first 19 sheets, may be it's because sheets are oddly numbered. To remind, I have sheet 1, and then sheets from 20 to 712. Is there any way to renumber those sheets 'normally' so their numbers became from 2 to 694?
In any case I was able to manage the rest manually, by moving all results in column D of combined sheet 18 rows down.
Then I manually copy pasted results from the first 19 sheets, so now have all phone numbers in tact and in correct spots.

I appreciate your help on that!

Also, can you twist your macros a little so it takes addresses in the same manner as it took phone numbers?

Again, addresses are all over the place in every sheet from 20 to 712, but they all have 1 same feature, i.e. text ", ON" in it. When macros finds ", ON" it should move that field into column E of the Sheet 1.

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

If no addresses found on some sheets, macros should skip that raw and move to the next sheet.

 

[JLGWhiz]

I don't believe I can offer anything more for this thread. I suggest you start a new thread if you have additional requirements.
Regards, JLG