How to move cells to another column based on a certain value

Greetings,
I am familiar with Excel but I am a newbie to macros. I am using Excel 2003 and I am working on a couple of spreadsheets that contains over 50,000 rows. My problem is this: I need to look at values in column "A" of spreadsheet 1 and compare them to the values in column "A" of spreadsheet 2. If the values match for a certain row, then I need to copy the value on column B of spreadsheet 1 and paste it to column B of spreadsheet two. That is, i need help in accomplishing the following (Just an example):

1. Look at values column A, spreadsheet 1
2. Compare to values in Column A, spreadsheet 2
3. if (lets say) row 4 in column A, sheet 1 matches row 10, column A, sheet 2 then...
4. ...copy values in row 4, column B, sheet 1
5. paste value to row 10, column B, sheet 2

I hope this does not sound too confusing... i know it is to me. Can someone perhaps help with this? I would truly appreciate it.

PS.- i could even paste all the values from one sheet to another so that I can have all the values in one sheet if this would make it easier. That way i would need to compare values in one column to values in another column in the same sheet.

April12

Hi Try this for a start, see how you get on.
code searches sheet(1) pastes in sheet(2)

Please Login or Register  to view this content.

Regards Mick

Thanks for the prompt response Mick.
I tried it and the macro seems to run without completing. I left it running for about an hour before stopping it. could this be because the sheets are so large?

Thanks,

April12

I've just found this posted to another Forum. "Old Age"
---------------------
Hi, you could use this code, which is the same but with screen updating held until the end of the code. This will reduce the time a bit.
For interest, I ran through just 50,000 lines without the screen updating, it took 3.125 Min and with the updating held 2.5 Mins.
But you will have to multiply the time for one Loop times the number of loops (which is the number of rows of information in column "A") for the correct time.
At the lower rate 1000 lines would take 0.05 Minutes times 1000 = 50 Minutes
I hope that right !
It could be quite a while.
Code:
Dim cl As Range, cl2 As Range
Application.ScreenUpdating = False
Dim rng As Range, rng2 As Range
Set rng = Range(Range("A1"), Range("A" & Rows.Count) _
.End(xlUp))
Set rng2 = Sheets("sheet2").Range(Sheets("sheet2") _
.Range("A1"), Sheets("sheet2") _
.Range("A" & Rows.Count).End(xlUp))
MsgBox rng.Rows.Count
For Each cl In rng
For Each cl2 In rng2
If cl.Value = cl2.Value Then
cl2.Offset(, 1).Value = cl.Offset(, 1).Value
End If
Next cl2
Next cl
Application.ScreenUpdating = True
[/code]
Regards Mick