Does anyone know how to do this?

It compares whatever is in Col. A in this case "sam" and "Tom" with any
matches in Col. A of compare sheet and if it finds one, it will copy and
paste the corresponding cells from master sheet into compare sheet. Now in
compare sheet there are only 2 sams and in master sheet there are 4 sams, so
it should find matches for 2 sams from master sheet and copy them to compare
sheet and the other sams below 2 sams in compare sheet.


Master sheet:

A B C D
sam 1 10 100
sam 2 20 200
sam 3 30 300
sam 4 40 400
Tom 5 6 7

Compare sheet:

A B C D
Sam
Sam
Tom

Compare sheet after running the program:

A B C D
Sam 1 10 100
Sam 2 20 200
3 30 300
4 40 400
Tom 5 6 7

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

Sub Macro1()
Dim iRow As Integer
Dim ws As Worksheet
Dim ws2 As Worksheet
iRow = 2
Set ws = Sheets("Master")
Set ws2 = Sheets("Compare")
Do
If ws.Cells(iRow, 1) < ws2.Cells(iRow, 1) _
Then ws2.Cells(iRow, 1).Insert Shift:=xlDown
ws.Range("B" & iRow & ":D" & iRow).Copy _
ws2.Range("B" & iRow)
iRow = iRow + 1
Loop Until ws.Cells(iRow, 1) = ""

End Sub

Hth,
Merjet