Merge two sheets

[BPR]

I have two sheets Sheet1 and Sheet2. In A1 in both sheets I have a key.

I need to:

1. When A1:A100 in Sheet2 find at match in A1:A100 in Sheet1 -> copy
specific cells from Sheet2 to Sheet1 .
2. When a key in Sheet2 is not found in Sheet1 -> copy the entire row in
Sheet2 to the end of Sheet1.

Can anyone give me a hint?

 

[tony h]

try
Sub a()
Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Worksheets(1).Range("A1")
Set rng2 = Worksheets(2).Range("A1")
Do Until rng1 = "" Or rng2 = ""
If rng1 < rng2 Then
Set rng1 = rng1.Offset(1)
ElseIf rng1 = rng2 Then
rng1.Offset(, 1) = rng2.Offset(, 1)
Set rng2 = rng2.Offset(1)
Else
rng1.EntireRow.Insert xlShiftDown
rng2.EntireRow.Copy rng1.Offset(-1).EntireRow
Set rng2 = rng2.Offset(1)
End If
Loop
MsgBox "done"
End Sub


regard