Find value in sheet 1 and copy matching row from sheet 2
 

Have tried to do this myself as a macro, and using the answers others have
had, but am stuck.
I have two sheets. Both have data arranged as headers across columns, data
down rows. The information is non sequential and can't be put in to the same
order (the lists are different lengths).
What I need to do is:

Select value in Sheet 2 column A
Find value in sheet 1
If found cut cells b,c,d from sheet 2
Paste into cells c,e,f in sheet 1
Delete row in sheet 2
Loop

The result is that if sheet 1 =
A B C D E F G
1 w 1 1 1
2 x 1 2 4
3 y 2 3 3
4 z 1 3 1

And sheet 2 =
A B C D
1 x 9 6 1
2 z 7 5 2
3 r 6 8 3

Then afterwards it would read

The result is that if sheet 1 =
A B C D E F G
1 w 1 1 1
2 x 1 9 2 6 1 4
3 y 2 3 3
4 z 1 7 3 5 2 1

And sheet 2 =
A B C D
1 r 6 8 3

(As r is not on the list so wouldn't be copied across.)

(And has anyone suggested to MS that Fixed width fonts on the CSS here would
be so wonderful! Actually will do it myself as every little helps...)

Thank you in advance any and all suggestions appreciated.

Find value in sheet 1 and copy matching row from sheet 2
 

Sub combinesheets()

Sh2RowCount = 2
With Sheets("Sheet2")
Do While .Range("A" & Sh2RowCount) < ""
ColAData = .Range("A" & Sh2RowCount)

Set c = Sheets("Sheet1").Columns("A:A").Find( _
what:=ColAData, LookIn:=xlValues)
If Not c Is Nothing Then
c.Offset(0, 2) = .Range("B" & Sh2RowCount)
c.Offset(0, 4) = .Range("C" & Sh2RowCount)
c.Offset(0, 5) = .Range("D" & Sh2RowCount)
.Rows(Sh2RowCount).Delete
Else
Sh2RowCount = Sh2RowCount + 1
End If

Loop
End With
End Sub


"Also" wrote:

Have tried to do this myself as a macro, and using the answers others have
had, but am stuck.
I have two sheets. Both have data arranged as headers across columns, data
down rows. The information is non sequential and can't be put in to the same
order (the lists are different lengths).
What I need to do is:

Select value in Sheet 2 column A
Find value in sheet 1
If found cut cells b,c,d from sheet 2
Paste into cells c,e,f in sheet 1
Delete row in sheet 2
Loop

The result is that if sheet 1 =
A B C D E F G
1 w 1 1 1
2 x 1 2 4
3 y 2 3 3
4 z 1 3 1

And sheet 2 =
A B C D
1 x 9 6 1
2 z 7 5 2
3 r 6 8 3

Then afterwards it would read

The result is that if sheet 1 =
A B C D E F G
1 w 1 1 1
2 x 1 9 2 6 1 4
3 y 2 3 3
4 z 1 7 3 5 2 1

And sheet 2 =
A B C D
1 r 6 8 3

(As r is not on the list so wouldn't be copied across.)

(And has anyone suggested to MS that Fixed width fonts on the CSS here would
be so wonderful! Actually will do it myself as every little helps...)

Thank you in advance any and all suggestions appreciated.