Well I haven't written the code I didn't realize that my current code
wouldn't pick up these numbers.

Sub Zurnprt2()

Const SummaryWorkbook = "ZurnOpenItemsspreadsheetXX.xls"
Const MainInvoiceCol = 5
Const MainPasteCol = 14
Const WbkInvoiceCol = 5
Const WbkStartCol = 1
Const WbkEndCol = 14

'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, MainInvoiceCol).End(xlUp).Row
Set InvoiceRange = wsh1. _
Range(Cells(1, MainInvoiceCol), Cells(Lastrow, MainInvoiceCol))

'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 E which is the 10th column
Lastrow = .Cells(Rows.Count, WbkInvoiceCol).End(xlUp).Row
Set InvoiceRange2 = _
.Range(Cells(1, WbkInvoiceCol), Cells(Lastrow, WbkInvoiceCol))

'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, WbkStartCol), _
Cells(cell2.Row, WbkEndCol)).Copy _
Destination:=wsh1.Cells(cell1.Row, MainPasteCol)


End If

Next cell2

End With

End If

Next wbk1
Next cell1

End Sub









Incidental wrote:
Hi there

I have to say i'm not entirely sure what you are trying to do with out
seeing an example of your code but you could check to make sure that
you are looking for xlWhole in the LookAt option find. When you
record a macro in excel using find it will automatically set the
LookAt option to xlPart. See the amended macro that i recorded

Cells.Find(What:="cmm123", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

This might be of some help to you if not you would have to paste some
of your code so i could have a look

S

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