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

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.
 
J

Joel

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top