Copying cell contents to rows based on key values in column

WikeMo1

New Member
Joined
Apr 15, 2019
Messages
19
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!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
In sheet2, put your data in B2 down

Try this:

Code:
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
 
Upvote 0
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?
 
Upvote 0
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 />
 
Upvote 0
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!
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Here is a variant of the vba above:
Code:
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!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top