Report

Transferring data between sheets

Ask a question n_sheikh 1Posts Wednesday November 23, 2016Registration date November 23, 2016 Last seen - Last answered on Nov 28, 2016 at 11:55 AM
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.
See more 
Helpful
+0
moins plus
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
Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!