Transferring data between sheets [Closed]

Report
Posts
1
Registration date
Wednesday November 23, 2016
Status
Member
Last seen
November 23, 2016
-
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
-
Hello,

Thanks in advance for reading my question.

I am have two excel sheets. The first sheet contains the columns: first name, last name and id while the second excel sheet contains first name, last name and school. Not all of the rows in the first sheet are in the second sheet, but there is an overlap.

I would like to write a macro to transfer ids from the first sheet to the second sheet. So for example, if the first sheet is as follows:

ID First Name Second Name
1 John Jacob
2 Anne Freedman
3 Will Klein

and the second sheet is as follows:

First Name Second Name School
John Jacob X
Will Klein Z

I would want the second sheet to look as follows after I run the macro:

ID First Name Second Name School
1 John Jacob X
3 Will Klein Y

I would so very much appreciate if someone can tell me how to do this or alternatively point me to resources.

Thank you in advance.

1 reply

Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
458
Hi n_sheikh,

The "Y" behind Will Klein in the result table is a typo, right?

See if the following code does as requested:
Sub RunMe()
Sheets("Sheet2").Select
Columns("A:A").Insert
Range("A1").Value = "ID"

Sheets("Sheet1").Select

For Each cell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
    x = 2
    Do
        If cell.Value & cell.Offset(0, 1).Value = _
        Sheets("Sheet2").Range("B" & x).Value & _
        Sheets("Sheet2").Range("C" & x).Value Then
            Sheets("Sheet2").Range("A" & x).Value = cell.Offset(0, -1).Value
            GoTo NextCell
        End If
        x = x + 1
    Loop Until Sheets("Sheet2").Range("B" & x).Value = vbNullString
NextCell:
Next cell
End Sub


Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!