Put Data from Excel File1 into the Excel File2

Question

I have 2 Excel files with Names of people (surname and firstname in the same cell) and in the second file i want to put the Data of the Excel File1 where the same name of someone is written and copy his job name which is written in the same row. I'll put a piucture so you guys can understand AS AN EXAMPLE. To let you guys know, i just have some of the employees in the Excel File2. In the Excel File1 there are about 1200 Employees, and in the Excel File2 only about 150. I need a VBA trick. :> Hope you enjoy the funny content.

EXAMPLE -> Click here for the graphical representation

Answer 1

You can do it without VBA, just by using INDEX and MATCH excel function. In your case you would need to put in Excel File2 B3 cell the following formula, and copy to the cells below.

=INDEX([Excel File1.xlsx]Sheet1!$A$3:$B$8,MATCH($A3,[Excel File1.xlsx]Sheet1!$A$3:$A$8,0),2)

The above formula is based that the name of the file is "Excel File1.xlsx", that the data are in "Sheet1", and that your data goes from row 3 to 8. So adjust accordingly. INDEX-MATCH combination is really useful, so I would advice you to read more about it, as well as doing a couple of exercises.

Answer 2

i got an ancient template that copies data from RAW sheet back to the original file. if you're looking for a way to cycle through 2 open files this will do the trick. host the code on the "File1" and run it there, modify the last part to the desired range.

Dim BookCounter As Integer
Dim wb, FirstWB, SecondWB As Workbook

Set FirstWB = ActiveWorkbook                'set the initial variables
BookCounter = 0



'*********** Looping to check if more than 2 files (FIRST + SECOND) are open ********
For Each wb In Workbooks
    BookCounter = BookCounter + 1
Next
If BookCounter > 2 Then
    MsgBox "Please Close all unnecessary files!" & vbNewLine & vbNewLine & "Make sure you keep only 2 files open while running this Macro.", vbOKOnly + vbInformation, "Too Many Files Open"
    Exit Sub
Else
    'nothing
End If


'*********** Looping to set the RAW workbook into your SecondWB variable **************
For Each wb In Workbooks
    If wb.FullName <> FirstWB.FullName Then
        wb.Activate
        Set SecondWB = ActiveWorkbook
    End If
Next



'------------- now that you've captured both files, you can move from
'--------------------- one to another with <variable>.activate cmnd, selecting the sheet + range, and copying whatever you need.

Dim FirstSht, SecondSht As Worksheet

FirstWB.Activate
    Sheets(1).Select
    Set FirstSht = ActiveSheet

SecondWB.Activate
    Sheets(1).Select
    Set SecondSht = ActiveSheet

'--------------------------- cycling through the "files" (now captured as your var.workbooks) needs to be done with <>.activate cmnd

FirstWB.Activate
FirstSht.Select
    FirstSht.Range("A1").Copy

SecondWB.Activate
SecondSht.Select
    SecondSht.Range("A100").PasteSpecial Paste:=xlPasteAll