I'm trying to figure out the right way of writing this idea, I need some help!
! Ok we start off in wrkbk A and start in cell A1(which is an invoice #) we
copy cell A1 and search for the contents of cell A1 in wrkbk B if found copy
cell + 9 cells to the left and past to cell M1 in wrkbk A. then Next line
repeat same process; if not found go back to wrkbk A and go to the next line
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 http://www.officekb.com

try this code.

Sub lookupbooks()


'this should be workbook a
Set wsh1 = ThisWorkbook.Worksheets(1)

wsh1.Activate
lastrow = wsh1.Cells(Rows.Count, 1).End(xlUp).Row
Set InvoiceRange = wsh1.Range(Cells(1, 1), Cells(lastrow, 1))

For Each cell1 In InvoiceRange

InvoiceNumber = cell1.Value
For Each wbk1 In Application.Workbooks

If StrComp(wbk1.Name, ThisWorkbook.Name) < 0 Then
With wbk1.Worksheets(1)
.Activate
lastrow = .Cells(Rows.Count, 10).End(xlUp).Row
Set InvoiceRange2 = .Range(Cells(1, 10), Cells(lastrow, 10))

For Each cell2 In InvoiceRange2

If (InvoiceNumber = cell2.Value) Then

.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


"Joe via OfficeKB.com" wrote:

I'm trying to figure out the right way of writing this idea, I need some help!
! Ok we start off in wrkbk A and start in cell A1(which is an invoice #) we
copy cell A1 and search for the contents of cell A1 in wrkbk B if found copy
cell + 9 cells to the left and past to cell M1 in wrkbk A. then Next line
repeat same process; if not found go back to wrkbk A and go to the next line
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 http://www.officekb.com

Will this look through all my wrkbks? the file names are wrkbk1, wrkbk2,
wrkbk3, etc. all the way to wrkbk10.

Joel wrote:
try this code.

Sub lookupbooks()


'this should be workbook a
Set wsh1 = ThisWorkbook.Worksheets(1)

wsh1.Activate
lastrow = wsh1.Cells(Rows.Count, 1).End(xlUp).Row
Set InvoiceRange = wsh1.Range(Cells(1, 1), Cells(lastrow, 1))

For Each cell1 In InvoiceRange

InvoiceNumber = cell1.Value
For Each wbk1 In Application.Workbooks

If StrComp(wbk1.Name, ThisWorkbook.Name) < 0 Then
With wbk1.Worksheets(1)
.Activate
lastrow = .Cells(Rows.Count, 10).End(xlUp).Row
Set InvoiceRange2 = .Range(Cells(1, 10), Cells(lastrow, 10))

For Each cell2 In InvoiceRange2

If (InvoiceNumber = cell2.Value) Then

.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


I'm trying to figure out the right way of writing this idea, I need some help!
! Ok we start off in wrkbk A and start in cell A1(which is an invoice #) we
[quoted text clipped - 3 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

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






"joe via OfficeKB.com" wrote:

Will this look through all my wrkbks? the file names are wrkbk1, wrkbk2,
wrkbk3, etc. all the way to wrkbk10.

Joel wrote:
try this code.

Sub lookupbooks()


'this should be workbook a
Set wsh1 = ThisWorkbook.Worksheets(1)

wsh1.Activate
lastrow = wsh1.Cells(Rows.Count, 1).End(xlUp).Row
Set InvoiceRange = wsh1.Range(Cells(1, 1), Cells(lastrow, 1))

For Each cell1 In InvoiceRange

InvoiceNumber = cell1.Value
For Each wbk1 In Application.Workbooks

If StrComp(wbk1.Name, ThisWorkbook.Name) < 0 Then
With wbk1.Worksheets(1)
.Activate
lastrow = .Cells(Rows.Count, 10).End(xlUp).Row
Set InvoiceRange2 = .Range(Cells(1, 10), Cells(lastrow, 10))

For Each cell2 In InvoiceRange2

If (InvoiceNumber = cell2.Value) Then

.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


I'm trying to figure out the right way of writing this idea, I need some help!
! Ok we start off in wrkbk A and start in cell A1(which is an invoice #) we
[quoted text clipped - 3 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

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

I made the changes you asked. You can make column changes as needed
Added Constant for columns to make it easier to change
Const MainInvoiceCol = 1 - Invoice column in 1st worksheet
Const MainPasteCol = 10 - where the copied data gets pasted
Const WbkInvoiceCol = 5 - Invoice Column on other workbooks
Const WbkStartCol = 1 - start column of copied data
Const WbkEndCol = 14 - end column of copied data




Sub lookupbooks()

Const SummaryWorkbook = "wrkbk1.xls"
Const MainInvoiceCol = 1
Const MainPasteCol = 10
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


"Joe via OfficeKB.com" wrote:

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

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