formula/macro to search sheet & copy data to specific sheets

I'm an entry level excel user and have a query on how to get excel to search a worksheet with different types of data and copy specific rows to separate sheets.

In the attached file, you can see 3 different sections- A, B & C, all with different column headings.

In all sections, entering the 'Code' should get the 'Name' to automatically appear (I think i've got that sorted by using a vlookup formula to search another sheet). In Section A, the formula will also automatically display the 'Ref' and 'Ref2'.

This sheet needs to be completed around once a week and the number of entries in each section will vary on each occasion. Therefore, any solution would have to be able to compensate for extra/fewer rows in each section.

What I need is to set up a master file with 3 sheets- 'A', 'B' & 'C', and I need some way of searching each new weekly file created, copy all the data from section A on the weekly sheet to worksheet A in the masterfile, copy all the data from section B on the weekly sheet to worksheet A in the masterfile etc.

Ultimately, the masterfile's 3 sheets would maintain a record of all the entries ever made.

Am I making sense? Is this possible with a macro (assuming each weekly file is named in a specific format)?

Can anyone please help?