Create Sub Totals and Sum Total

Hi all

Attached is an example of the workbook I am looking for

What I have created so far :

A macro will take the sheet "Data" and split the info across into new separate worksheets labelled "SIM","MOR","RED","CAR","EMB"

The split worksheets contain the same info as "Data" however the column for "Total Arrears" is sorted as descending and the moment values become negative, there is a 5 row gap to seperate the Positive amounts and Negative amounts in a descending order

What needs to be completed:

I need to create Sub Totals in columns E:K for both the negative and Postive balances (and Label them as "Sub - Total" in column D) as well as have them represented as a percentage, and then after that have a grand total value represented at the bottom

I have attached examples of what the final product should look like for each worksheet (they are labelled in blue)

The problem I have is that I am unsure of how to use VBA to recognise where to put the amounts for Sub Totals. I would assume it would use a .End.xlUp.Row and some kind of Offset, but I don'r even know where to begin

The "Data" sheet has a button that runs the macro to split the data into worksheets, but the worksheets SIM/MOR/RED/CAR/EMB must be deleted first since there cannot be duplicate sheets (which is why there is also a button to delete those sheets)

Help with this matter is appreciated

Regards