How to automate cleansing of file and creation of sub files from a national dataset to contain only specific sites dats

I receive a file monthly from my national finance team containing all the information for one of our project areas. I am the regional head but should be sharing the data to relevant project leads at individual sites (ie south east 2 gets a report and South East 3 gets a different report).

Currently I just eyeball the report look at the big issue sites and then cut the report for each one save a copy reopen and repeat then send them to the respective leads. What I want to do is create a cut of each sites data save this as a copy for that sites name and cycle through for all my sites (around 26 at last count) I would also need to clear the pivot cache to fully remove the national data in each sites file.

I assume this could be done with a macro but can't work out the generic layout of it. I would need to clean sheets 2 and 3 both have filters ranges set just nothing filtered. Sheet 2 should only have items containing south east in column b left before creating individual site files. Whilst sheet 3 has this data in column a. Sheet 3 data needs trimming from row 5 whilst 2 is from row 4.

Tge long term aim would be to move this across from macro to power automate so I can also automate the sending of the files and saving to sharepoint but am less understanding of office scripts than vba so want to get the first one of a macro without saving down first so I can then compare it to the work flow output to check it's all as expected.

Is anyone able to help me advise on this one