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]

Here is a variant of the vba above:
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!

Sorry, but I still do not understand what data you have on the sheet, what you want to review and what you need.
You could give complete examples, with real data.
Give a complete example of what you have.
And then you put the result you expect.

 

[WikeMo1]

I am sorry. I can’t post my real data. The example I have shown works exactly the same as on my real data.

 

[DanteAmor]

I am sorry. I can’t post my real data. The example I have shown works exactly the same as on my real data.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[WikeMo1]

Sorry. I seem to have completely misunderstood.

If you mean to post it so you have a file to work with? Ok.
https://www.dropbox.com/s/oks26ss64wktifo/Macro-test-1.xlsm?dl=0

If this does not contain enough data, or if the data is not the proper type to mean anything for this, I will add in more data or change it.

Sheet1 has some dummy data.
Sheet2 has a list of values that correspond to the "key" in Sheet1. (The values in Sheet2 only go up to "k" and this is OK. I don't always have the same amount of values in the two sheets. In fact, I never do.)

What the vba "Copying_cell" does, of course, (this is a colloquial description) is seek to match values in column B2 of Sheet2 to an instance of a corresponding value in column B2 in Sheet1. If it finds a value in Sheet1 column B2, it copies the cells in the row of the matched value in Sheet2 to the row with the first instance of the matching value in Sheet2.

There are multiple instances of the values "e" and "g" in column B2 of Sheet1, only the first instance of either will be affected by this macro, regardless of sort order.
What I would want is that the cell values found in Sheet2 are copied in the rows for EVERY instance of a corresponding "key" value found in Sheet1.

What needs to be modified so that all instances of "e" and "g" (in this example) will be accounted for?

Let me know if this type of presentation is what you mean.

And thanks for taking the time to even look at this.

 

[DanteAmor]

There are multiple instances of the values "e" and "g" in column B2 of Sheet1, only the first instance of either will be affected by this macro, regardless of sort order.
What I would want is that the cell values found in Sheet2 are copied in the rows for EVERY instance of a corresponding "key" value found in Sheet1.

What needs to be modified so that all instances of "e" and "g" (in this example) will be accounted for?

Try this

Sub Copying_cell()
    Dim c As Range, r As Range, f As Range, cell As String
    Set r = Sheets("Sheet1").Range("B2", Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp))
    For Each c In Sheets("Sheet2").Range("B2", Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp))
        Set f = r.Find(c, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            cell = f.Address
            Do
                f.Offset(0, 1).Resize(1, 3).Value = c.Offset(0, 1).Resize(1, 3).Value
                Set f = r.FindNext(f)
            Loop While Not f Is Nothing And f.Address <> cell
        End If
    Next
End Sub

 

[WikeMo1]

OK great. Many thanks! I shall try that soon.

 

[DanteAmor]

OK great. Many thanks! I shall try that soon.

Ok, tell me any results.