Copying cell contents to rows based on key values in column

[WikeMo1]

Hello All,
This is my first post and I really hope someone can be of help. I normally try and eek out success with Excel but this has got me stumped. Everything I find seems to just fall to the side of what I need to do.

Here is a quick example.

Let's say I have Sheet 1 that has this data


with a code (let's call it Info 1), a "record key," a name, a location, and a phone number.


And let's say I have Sheet 2 with this (new) data:


Same columns as the first one starting with column B (it actually doesn't have the Info 1 column).


I want to match rows in Sheets 1 and 2 that both contain the "key" found in column B, and copy the the content from those matching rows into Sheet 1 so I end up with this (for example):


Is this hard to do? I would have to do this over an immense amount of rows in many workbooks.
Seems like it shouldn't be hard to do.


Thank you for any help!

 

[DanteAmor]

In sheet2, put your data in B2 down

Try this:

Sub Copying_cell()
    For Each c In Sheets("[COLOR=#0000ff]Sheet2[/COLOR]").Range("[COLOR=#0000ff]B2[/COLOR]", Sheets("[COLOR=#0000ff]Sheet2[/COLOR]").Range("[COLOR=#0000ff]B[/COLOR]" & Rows.Count).End(xlUp))
        Set b = Sheets("[COLOR=#006400]Sheet1[/COLOR]").Range("A:A").Find(c.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not b Is Nothing Then b.Offset(0, 1).Resize(1, 3).Value = c.Offset(0, 1).Resize(1, 3).Value
    Next
End Sub

 

[WikeMo1]

Ok, great! Thanks, I will try it out a bit later tonight.
Do you mean shift my data over so that the “key” column is B, the same as in Sheet 1? So all columns align?

 

[DanteAmor]

No.
Sheet 1 remains the same.
only on sheet2 the data should start in cell B2:

Like this:
<b>sheet2</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:101.7px;" /><col style="width:76.04px;" /><col style="width:81.74px;" /><col style="width:67.49px;" /><col style="width:68.44px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td style="background-color:#92d050; ">KEY</td><td style="background-color:#92d050; ">NAME</td><td style="background-color:#92d050; ">LOCATION</td><td style="background-color:#92d050; ">PHONE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >START DATA ---></td><td style="text-align:right; ">32</td><td >new name</td><td >new loc</td><td >new fon</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td style="text-align:right; ">89</td><td >new name89</td><td >new loc89</td><td >new fon89</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td style="text-align:right; ">2</td><td >new name2</td><td >new loc2</td><td >new fon2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td style="text-align:right; ">4</td><td >new name4</td><td >new loc4</td><td >new fon4</td></tr></table> <br /><br />

 

[WikeMo1]

Ok, I understand.

Thanks! Will try that out soon.

 

[WikeMo1]

Wow. Seems to work great so far.
Now I just have to figure out what it's truly doing.

(VBA newbie here)

Thanks again for your help!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[WikeMo1]

I am resurrecting this because I wondered what I would have to add to make this find and match multiple instances in the range and not just the first one. I tried adding in a .FindNext but I really am not sure where it would go.

 

[DanteAmor]

I am resurrecting this because I wondered what I would have to add to make this find and match multiple instances in the range and not just the first one. I tried adding in a .FindNext but I really am not sure where it would go.

If it is a request on the same subject, then you could explain with examples, what you have and what you expect of result.
But if it's a new topic, then you could create a new thread; and in the same way you could explain it with examples.

 

[WikeMo1]

Here is a variant of the vba above:

Sub Copying_cell()
    For Each c In Sheets("Sheet2").Range("B2", Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp))
        Set b = Sheets("Sheet1").Range("B2:E16").Find(c.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not b Is Nothing Then b.Offset(0, 1).Resize(1, 3).Value = c.Offset(0, 1).Resize(1, 3).Value
    Next
End Sub

Let's say I want to replace any and all rows in "Sheet1" that have the Key value of "g" in column B2, with the proper values found in "Sheet2."

Sheet 1:


Sheet 2:


When I run the vba, it only copies in the values to the first instance.



What would I have to change to make it copy the values to all instances?

Thanks in advance!