hey there the process that you did below worked but how would I change that
so that instead of taking two off spreadsheet 1 to compensate for YM WM. to
take off two in spreadsheets 2 -9 ?

Incidental wrote:
Hi there

I think I have a better idea of what your problem is now. The invoice
numbers in column A of the summery workbook will contain a number like
YM456789 and you want to search for just the number portion of that
reference 456789 with the prefix CMM in front of it like CMM456789 in
the other nine spreadsheets???

If this is the case you can pass the cell reference you want to search
for to a string and then remove the characters you don't want then
search for the remained with the added prefix of CMM.

Sub Zurnprt2()
Const SummaryWorkbook = "ZurnOpenItemsspreadsheetXX.xls"
Const MainInvoiceCol = 5
Const MainPasteCol = 14
Const WbkInvoiceCol = 5
Const WbkStartCol = 1
Const WbkEndCol = 14
'**************** added line below
Dim i As Integer ' IT IS A GOOD IDEA TO ALWAYS DECLARE YOUR VARIABLES

'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
'**************** added 2 lines below
i = Len(InvoiceNumber) 'count the characters in the string
i = i - 2 'Remove 2 from the count to accommodate YM, WM or XM
InvoiceNumber = Right(InvoiceNumber, i) 'counting back from the right
'select only the numbers

'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
'**************** amended line below
If (cell2.Value = "CMM" & InvoiceNumber) Then 'add the
prefix and the number here
'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

End Sub

if you want to test this see if it helps you out, if you still have
trouble reply and i will see what i can think of

S

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