Search multiple worksheets and return value to main worksheet

[Jane Doe]

Need to find value in column P of multiple worksheets (i.e. may_acct1,
may_acct2, may_acct3) based on phone number in column A of these worksheets
matching phone number in column F of main worksheet and return that value
from column P to column B,C, or D based on worksheet month (may_acct1,
june_acct1 or may_acct2, june_acct2).

Worksheet format to search

column
A.........................................................................columnP
Subscriber
Number...........................................................Subscriber
Total



Worksheet format to return results

column A...(column B, C and D)..................column
E...................column F
Subscriber Total
Name May June July Active Carrier
Subscriber Number

 

[Joel]

Try this code

Sub CreateaMain()

ShtNames = Array("may_acct1", "may_acct2", "may_acct3")
With Sheets("Main")
LastRow = .Range("F" & Rows.Count).End(xlUp).Row
For ShtNum = 0 To 2
Set Sht = Sheets(ShtNames(ShtNum))
For RowCount = 1 To LastRow
PhoneNum = .Range("F" & RowCount)
Set c = Sht.Columns("A").Find(what:=PhoneNum, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
.Cells("B" & RowCount).Offset(0, ShtNum) = _
Sht.Range("P" & c.Row)
End If
Next RowCount
Next ShtNum
End With
End Sub

 

[Jane Doe]

Joel:

I appreciate the help, but this is not clear to me. I am not much good with
excel scripting. Can you explain in more detail where I need to change to
fit my worksheets (i.e. "For ShtNum = 0 to 2 do i need to change to my sheet
names I will be searching? may 08_acct1, june 08_acct2, may 08_acct2, june
08_acct3, etc...

Thank you!

 

[Joel]

I made a slight change in the code to handle a vairable amount of sheets

You can place as many sheet names as required into the line below.
ShtNames = Array("may_acct1", "may_acct2", "may_acct3")

All you need to do is to modify the line above putting in the number of
sheets you want to search. The first sheet results will go into column B,
the next in column C up to the number of sheets you put into the array
statement.

You also need to make sure the Main sheet name is corect in the statment
below. You can change the name in this line to match the summary sheet name

With Sheets("Main")


----------------------------------------------------------
Sub CreateaMain()

ShtNames = Array("may_acct1", "may_acct2", "may_acct3")
With Sheets("Main")
LastRow = .Range("F" & Rows.Count).End(xlUp).Row
For ShtNum = LBound(ShtNames) To UBound(ShtNames)
Set Sht = Sheets(ShtNames(ShtNum))
For RowCount = 1 To LastRow
PhoneNum = .Range("F" & RowCount)
Set c = Sht.Columns("A").Find(what:=PhoneNum, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
.Cells("B" & RowCount).Offset(0, ShtNum) = _
Sht.Range("P" & c.Row)
End If
Next RowCount
Next ShtNum
End With
End Sub


------------------------------------------------------------------------------------------

 

[Jane Doe]

I received a Runtime 13 type "mismatch error" ???

Sub CreateaMain()

ShtNames = Array("May 08_478156199", "May 08_4614445456", "June
08_478156199", "June 08_461445456", "July 08_478156199", "July 08_461445456")
With Sheets("Phones_Analysis_9-2008")
LastRow = .Range("F" & Rows.Count).End(xlUp).Row
For ShtNum = LBound(ShtNames) To UBound(ShtNames)
Set Sht = Sheets(ShtNames(ShtNum))
For RowCount = 1 To LastRow
PhoneNum = .Range("F" & RowCount)
Set c = Sht.Columns("A").Find(what:=PhoneNum, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
.Cells("B" & RowCount).Offset(0, ShtNum) = _
Sht.Range("P" & c.Row)
End If
Next RowCount
Next ShtNum
End With
End Sub

 

[Joel]

Sorry it was a stupid mistake

from
..Cells("B" & RowCount).Offset(0, ShtNum) = _
Sht.Range("P" & c.Row)
to
..Range("B" & RowCount).Offset(0, ShtNum) = _
Sht.Range("P" & c.Row)



the cells method uses Row Number and Column Number which Range uses a Letter
and Number

Cells Methods

Cells(2,3)


Range Method

Range("C2")

 

[Jane Doe]

Thanks for responding Joel. I tried that and got error'9' subscript out of
range.

What should I check?

Thanks

 

[Joel]

I think you have at least one of the sheet names spelled wrong. The names
on the sheet names in the workbook should not have any double quote or spaces
at the beginning or the end and make sure you have underscores (_) in the
same place on the worksheet and macro. The sheet names can have spaces but
must match exaclty the same in the macro and the speadsheet.

The code must be working for at least one worksheet because the original
error was occuring on the following line

.Cells("B" & RowCount).Offset(0, ShtNum) = _
Sht.Range("P" & c.Row)

I think the new error should be on this line

Set Sht = Sheets(ShtNames(ShtNum))

It should be highlighted in color when the error occurs. fix the sheet
names. Also check you worksheet to see if any of the values filled in on the
sheet Phones_Analysis_9-2008.