Assist with automating excel report using VBA

I have a task that requires an excel workbook to be formatted to report on business KPIs.
Raw data from a tool is imported into sheet 1 and this data has been split up into separate sheets by brand. The Article points, submitted on time, % draft delivered on time, Article published on time? and % articles published on time columns are then inserted into each sheet.
Each sheet needs to be sorted by writer, where the subtotal for each writer’s KPIs can be seen in the row below them.
This includes the total “Article Points”, total numbers of “Yes” in the “Published”, “Submitted on” and “Published on” columns and the % of “Yes” in the columns adjacent.
There is a set of rules that determines how many “Article Points” each writer gets scored per article.
On the final KPI points sheet, I require each writer’s total article points across all the brands to be in the “Volume Score” column. “Days Worked” will be input manually.
The “score” and “Publish score” columns are populated from the Score Guide at the top of the sheet depending on which adjustment is required (e.g. Grad writer, writer etc…). This information will be provided.
All other columns have the formulas visible in the sheet.
All rules and variables for any calculations will be provided as well as a copy of what the workbook looks like and what the final product should be.
VBA and macros have been used to get the sheet to its current state. It should be possible to enter new data into sheet1 and run a macro/s that results in the final version of the report.
Current Version.xlsx shows the current progress of this project.
The “ExpertMarket” sheet in Final Version.xlsx show what I would like each brand sheet to look like. The KPI Points sheet shows how this sheet should look when complete.
Rules.xlsx has the information needed to award the correct points to each article and writer.