Macros to retrieve phone numbers from multiple sheets on the combined sheet

kleinermuk

New Member
Joined
Nov 3, 2020
Messages
30
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 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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You can give this a try. It tested OK based on the OP info.

VBA Code:
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 Member
Joined
Nov 3, 2020
Messages
30
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA 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
 
Last edited:

kleinermuk

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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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
 

Forum statistics

Threads
1,136,732
Messages
5,677,439
Members
419,693
Latest member
divtjd

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