Would still being doing this?

Sub AoSmithprt2()



Const Summaryworkbook = "AOSMITHOpenItemsSpUPDATE.xls"
Const MainInvoiceCol = 7
Const MainPasteCol = 20
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



Gary Keramidas wrote:
one way:

Sub test()
Dim rngfound As Range
Dim i As Long
Dim firstaddress As String

For i = 1 To Worksheets.Count
With Worksheets(i).Range("A1:m500") '
Set rngfound = .Find(What:="123", LookIn:=xlValues, _
lookat:=xlPart)
If Not rngfound Is Nothing Then
firstaddress = rngfound.Address
Do
Debug.Print rngfound.Address
MsgBox "found at " & Worksheets(i).Name & " " & _
rngfound.Address
Set rngfound = .FindNext(rngfound)
Loop Until rngfound.Address = firstaddress
End If
End With
Next
End Sub

Hi it's me again!!
I had a quick question does anybody have any suggestions on how to run a
[quoted text clipped - 9 lines]

so take it found the match take coulmn a - o and place in wrkbk 1 in column o

--
Message posted via http://www.officekb.com