How to merge data from two excel workbooks into a third workbook and update automatically

Hi All

I am working on creating a database for the quotation team in our company. My project has 2 parts.

PART 1: We have two workbooks currently which record all quotations done for AIR transport and SEA transport. I need to merge the data from these workbooks into a master database which then can be accesses but different users. The master database should update any new quotes in either AIR or SEA automatically.

Attached are the source workbooks - Book2 AIR and Book3 SEA. The data from these has to be combined into Book4 Master.

In the Master workbook, the data from both Air & Sea should merge to appear as shown. When there is a new entry in either AIR or SEA it should appear at the end of the last entry for AIR or SEA. Refer cell highlighted in yellow.

I have tried to do this using the Paste Link function but it makes the file to heavy and does not really update automatically unless tables are extended to include empty cells but then that causes too many blank cells in the master - so not a suitable option. Is there a better solution using code or formulas or macros that can achieve this easily?

PART 2: From the master database created, I need the data to be split into 4 different workbooks based on UK branch (includes data for AIR & SEA) - so one for Heathrow, Purfleet, Tamworth and Manchester so each branch can view only their data and not the entire sheet.