Need help consolidating and manipulating data into two workbooks

Hi there,

I'm trying to transfer data between 2x workbooks. I need to take the highlighted data from the below workbook (currently split between different 'agent' tabs) and consolidate that data into a second workbook where it is then split down further depending on what type of policy an agent has sold (ie. in workbook 1 all policies are in 1 column, however in workbook 2 they need to split into different columns depending on whether they start with AB, CD or EF).



In workbook 2 you can see an example of how the new data needs to be presented. 



I also need to find out if it's possible to make it so that if an agent updates workbook 1, workbook 2 will automatically update itself as well without me having to do it manually?

Thanks everyone, I appreciate the help. If I've not explained myself fully (tends to be an issue) let me know so I can try to be more clear about what it is I need to do.