I have two worksheets in two different workbooks - the worksheets are
named "EARNED" and "Pay App Qty's". Each of these worksheets are
fairly complex and pull values automatically from OTHER worksheets
contained within their respective workbooks.

Column A in both worksheets contains specific Item Numbers - what I
need is code that will compare column A of both worksheets and when a
match is found copy a quantity from the appropriate cell in Column Z of
"Pay App Qty's" and paste it into the appropriate cell
in Column C of "EARNED".

"Pay App Qty's" contains multiple hidden rows - ONLY the visible rows
need to be included in the comparison.

Column Z of "Pay App Qty's" pulls figures from other worksheets within
it's workbook.

Item Number data starts in cell A7 of "Pay App Qty's".
Quantity data starts in Cell Z7 of "Pay App Qty's".

Item Number data starts in cell A2 of "EARNED".
Quantity data starts in cell C2 of "EARNED".

Data in "Pay App Qty's" currently goes to row 446.
Data in "EARNED" currently goes to row 271.

I am using Excel 2002 SP-2 and running Windows XP Professional.

Any and all help is sincerely appreciated.


Thanks!

- Kobi

Copying these 2 worksheets into a new workbook and naming them "sheet1"
and "sheet2" is an option.

Anyone have any idea on this?


Thanks!

- Kobi

Bump

Sub Compare()
Dim TB1 As Worksheet, TB2 As Worksheet
Dim R1%, R2%, i%, y%
Set TB1 = Workbooks("testbook1.xls").Worksheets(1) 'You added the file
name
Set TB2 = Workbooks("testbook2.xls").Worksheets(1) 'You added the file
name
R1 = TB1.Cells(Rows.Count, 1).End(xlUp).Row
R2 = TB2.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To R1
For y = 1 To R2
If TB1.Cells(i, 1) = TB2.Cells(y, 1) Then
TB2.Cells(y, 1) = "Match" 'puts match
in A column

Exit For
End If
Next y
Next i

End Sub



Now you will need something to put the contents in the cell after you compare.
YOu need to move to that column.
ActiveCell.Offset(0, 25).Range("A1").Select '25 moves from A over to Z
PayAppQty= ActiveCell.FormulaR1C1 'content of Z cell placed in
payappqty

then move back...
ActiveCell = PayAppQty

Place this last bit of code in your sub as needed to fit your files.

" wrote:

Bump