Split one worksheet into multiple worksheet by column

Thanks for reading this query.

I have a consolidated income statement for all the group companies. The particulars are in column A, the relevant numbers for each entity in the subsequent columns,(Please refer "Report" tab of the attached file) for example.

A B C D E
1 Particulars Entity 1 Entity2 Entity 3 Entity 4
2
3 Total revenue 1000 2000 3000 4000

I need to split this worksheet into 1 worksheet per entity and Column A needs to be included on all worksheets, for example - Columns A & B for Entity 1, Columns A & C for Entity 2, Columns A & D for Entity 3, and so on.

If possible the new worksheets could be named after the Entity names in Row 1, so that they are easily identifiable. I have around 500 companies on the list and hence need a macro to split the worksheet. I hope I am not asking for too much.

If copying column A is not possible, its ok. I would still be happy if I have 500 sheets with the numbers for that individual entity. I can then use the group feature of Excel and copy/past column A.

Please see the attached workbook for more clarity on the problem. The Report tab is what I currently have. The remaining 5 tabs are what I want to achieve.

Any help is appreciated. Many thanks in advance.

Kind regards,

Vijay

I did search the forum, but I found macros which would copy rows into multiple worksheets based on values in a particular column. I din't find anything similar to my requirement.