Combining two or more excel worksheet to a master file

[ngwyjng]

I am not an expert in excel macro or rather in any programming language.

I am trying to combine two or more excel worksheet to a master file. For example, i have a file which i name it as Team 2. Inside Team 2 file, it consist of various worksheet and 1 master file. For example if there is 3 members inside Team 2, my team 2 file will consist of one Team 2 master, member 1, member 2 and member 3 worksheets. Each of the members will enter their information inside their own file.

Now my boss do not want to go individually to see their update, he want to consolidate everything together and view it at Team 2 file, i having been using this formulae ='Member 1'!J6, but it is very time consuming and tedious, Is there any way that a macro can help.

I have attached the file for your reference.

Inside the file, Member 1 will need to enter the information highlighted in yellow from Column E onward and the information that enter will auto populated into Team 2 Pivot table.

Thanks and Regards
June