Hello,

I have 2 different excel files. The first file students.xls, has many names, surnames and the emails of my students. In D column I use the CONCATENATE to join the two columns to one. I have a second file info.xls in excel that I store some notes about some of the students about their attendance. Most of the sheets are named by a student's name (not all of them). What I want is a macro, that takes the name of a sheet in the second file lets say that a sheet is called James Peter, opens the first file that has all the names and emails, finds that name, and copies only his email in the a1 cell of the second file automatically. Can anyone help me?

students.xls :

Name Surname EMAIL Full name
Peter James test1@test.com James Peter (=CONCATENATE(B2;" ";A2))
John Papas test2@test.com Papas John
Nick Jones test3@test.com Jones Nick