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

  • Thread starter Thread starter Also
  • Start date Start date
A

Also

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.
 
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
 
Back
Top