Retrieving information from a read only list of tables

The sheet attached is a sample I just whipped up to show the format. There is a long list of programs with the associated engineers assigned to that program. I am looking at the master list and wanting to pull the manufacturing engineer's (ME) name from each program. This is all taking place on a different sheet. I want to have a drop down list for the program that will then retrieve the ME's name and email address so that when changes are made to the item pick list the form can be filled out and will auto-email the changes to the correct ME. The portion of the form I am having trouble with is selecting the correct ME after selecting the program name. The master files contains the latest reassignments and so that would be the file I need to have my drop down list point to. Again the attached sheet is the sample of that master file. The form has been included on that sheet as a visual reference of what I'm trying to make work, one step at a time.

thanks for any feedback. If there is an associated post that I missed that addresses this issue please point me towards it.


sample master file with form.xlsx

How about this for a start. What do I need to do to have this macro search all of column a and return all the values of column b that corespond to the title "Name:" so that I have a list of all program names. Then I can work on getting the ME names after that. Oh, and I just noticed that the spacing between the programs is not equal so that may create problems but again one step at a time.

    Windows("Master file.xls").Activate
   Do While Range("A1").Value = "Name:"
          Range("B1").Select
    Selection.Copy
    Windows("Picklist Communication Tool.xlsm").Activate
    ActiveSheet.Paste
    Loop
    
   End

This thread Formula or macro: If column A = X then column Y = Y addresses a large portion of my problem save that I am trying to take information from one sheet and copy to another sheet. As you can see above I have been trying different things. I like the answer from the other thread, but am not certain how to apply it to my situation. If column A in one sheet is equal to "Name:" then I want to copy the contents of the adjacent cell to my new work book. After that I will take that information and use it to populate column C with the ME name for that program. An example exists in my original post to this thread. I appreciate feedback and will continue to experiment with different things until I get it to work. Thanks.