The wrkbks that are getting searched the invoice is in column E so that would
be the 5th right? Also i would need that entire row copied A - N and placed
into Column J in wrkbk A...

Joel wrote:
I added comments to the code I sent yesterday. i also made one small change
to put your workbook name into the code

the code check only the first worksheett in every workbook. It expects the
summary workbook name to be wrkbk1. It looks at every invoice number in
column A on this workbook.

The it check every other open workbook for the invoice number. The statement
"For Each wbk1 In Application.Workbooks" gets every open workbook.

These workbook are checked in column J (10th column) for the invoice number.
the instruction siad 9 cells to the left so I asumed the Invoice number was
the 10th column. these 10 cells are copied to the first workbook. the
invoice number end up in two columns in the first workbook following your
instructions.

I wasn't sure what you meant by the entire workbook is checked for the
invoice number. Is the Invoice Number only in one column? If not describe
how tthe columns are laid out. Is the same type data repeated every 11
columns witth a empty column between the data? Let me know how it works.
chhange can be easily made to the code.

Sub lookupbooks()

Const SummaryWorkbook = "wrkbk1.xls"

'this is the first workbooks that has the invoice nubers in column A
Set wsh1 = Workbooks(SummaryWorkbook).Worksheets(1)

'the code below sets InvoiceRange to contain all the Invoice Numbers
'In column A
wsh1.Activate
lastrow = wsh1.Cells(Rows.Count, 1).End(xlUp).Row
Set InvoiceRange = wsh1.Range(Cells(1, 1), Cells(lastrow, 1))

'Now we loop though each of the Invoice Numbers in the 1st workbook
For Each cell1 In InvoiceRange

InvoiceNumber = cell1.Value
'Now Loop through all the open workbooks
For Each wbk1 In Application.Workbooks

'skip the 1st workbook
If StrComp(wbk1.Name, SummaryWorkbook) < 0 Then
With wbk1.Worksheets(1)
.Activate

'sets InvoiceRange2 to contain the invoicenumbers in
'column J which is the 10th column
lastrow = .Cells(Rows.Count, 10).End(xlUp).Row
Set InvoiceRange2 = .Range(Cells(1, 10), Cells(lastrow, 10))

'Now loop through all the Invoice Number checking again
'Invoice Number found in 1st workbook
For Each cell2 In InvoiceRange2

'Compare Invoice Numbers
If (InvoiceNumber = cell2.Value) Then

'copy Cells if the Invoice Number matches
.Range(Cells(cell2.Row, 1), Cells(cell2.Row, 10)).Copy _
Destination:=wsh1.Cells(cell1.Row, 2)


End If

Next cell2

End With

End If

Next wbk1
Next cell1

End Sub


Will this look through all my wrkbks? the file names are wrkbk1, wrkbk2,
wrkbk3, etc. all the way to wrkbk10.
[quoted text clipped - 48 lines]
and repeat. Oh yeah when it's searching for content of cell A1 the invoice #
may be in wrkbk B,C or D... any ideas?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200703/1