VBA help - Excel table

[GeorgeTimes]

Hi all,

I need your help with a table I'm trying to build and need to run it every Monday. I think this can be achieved with VBA.

What I need to do:
- At the beginning of every month, the table needs to erase the details (or simply don't take in consideration the values as I only care about the current month)
- I also need to run the report every Monday, therefore I need to store somewhere the figures as those need to be present in the table for the current month
- I need to find the top 3 countries that have the highest debt at the beginning of the Month(This will be taken from the tab called "Source Data". Think this can be done by a Pivot table where I'll look for the SUM of column L "Total 90+ EUR"
- From those 3 top countries, I need to extract the top 5 companies for each country (Not sure how this can be done). However there might be exceptions so this leads to the below:
- Assuming running this on first Monday of the month (02/01/2023), I get the top 5 companies for each top 3 countries.
Next Monday (09/01) I'll run this again and I get the same 5 companies so everything stays the same, I'm just going to add the figures for this Monday (i.e the debt in pounds).
The 3rd Monday (16/01) however, for one of those 5 companies (see Company C) the debt drops from around £91k to £23k and that company is not within the top 5. I will need that company to still remain in my table but I also need to add an extra company that's now on my top 5 list (Company F in this example)
- you can see from example I've attached that UK has 6 companies, while RFR (France) has 5 companies


Any idea how this can be achieved with or without VBA?

Many thanks in advance

example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	COLLECTOR_NAME	COLLECTOR_ALIAS	PARTY_NAME	ACCOUNT_NUMBER	ACCOUNT_NAME	RU Number	Operating Unit Name	Currency_code	Analyst	Country	RSS	Total 90+ EUR
2	UKIK01	A	Company A	100000181	Company A	289936783	UK	GBP	Agent 1	United Kingdom	UK	€ 9,220.09
3	UKIK01	A	Company B	90021323000794	Company B	289936783	UK	GBP	Agent 2	United Kingdom	UK	-€ 343.77
4	IECC60	A	Company C	10000523033	Company C	289936783	IE	EUR	Agent 3	Ireland	UK
5	BE300.MA	B	Company D	250021417174	Company D	001307081	RBE	EUR	Agent 4	Belgium	UK
6	MARINA.JORDAN RGSE	C	Company E	1004537456	Company E	315034629	RDE	EUR	Agent 5	Germany	UK	€ 255,450.15
Source Data

example.xlsx
	A	B	C	D	E	F	G	H	I
1	Sum of Total 90+ EUR
2	UK	BA	TL	02/01/2023	09/01/2023	16/01/2023	23/01/2023	30/01/2023	Weekly Comments
3	Company A	Agent 1	TL 1	£ 122,478.38	£ 121,519.89	£ 110,815.29
4	Company B	Agent 2	TL 2	£ 98,514.95	£ 98,420.18	£ 98,139.99
5	Company C	TBC	TL 3	£ 91,733.20	£ 91,678.77	£ 23,915.53
6	Company D	Agent 3	TL 4	£ 89,167.26	£ 69,687.55	£ 69,489.15
7	Company E	TBC	TL 3	£ 62,137.17	£ 62,237.31	£ 62,060.13
8	Company F	Agent 4	TL 6	£ -	£ -	£ 60,946.74
9	Sum of Total 90+ EUR
10	RFR
11	Company G	TBC	TL 3	£ 151,135.50	£ 151,135.50	£ 151,135.50
12	Company H	TBC	TL 3	£ 108,173.03	£ 108,173.03	£ 107,900.40
13	Company I	TBC	TL 3	£ 91,324.75	£ 91,324.75	£ 91,324.75
14	Company J	TBC	TL 3	£ 97,407.19	£ 95,339.20	£ 89,224.69
15	Company K	TBC	TL 3	£ 98,660.48	£ 98,660.48	£ 85,786.87
16	Total			£ 1,010,731.91	£ 988,176.66	£ 950,739.04
Table

Cell Formulas
Range	Formula
A2	A2	=INDEX('[Debt Dashboard v0.2.xlsb]REFERENCE'!$I$15:$I$34,1)
D2	D2	=DATE(YEAR(TODAY()),MONTH(TODAY()),8)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),6))
E2	E2	=DATE(YEAR(TODAY()),MONTH(TODAY()),15)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),6))
F2	F2	=DATE(YEAR(TODAY()),MONTH(TODAY()),22)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),6))
G2	G2	=DATE(YEAR(TODAY()),MONTH(TODAY()),29)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),6))
H2	H2	=DATE(YEAR(TODAY()),MONTH(TODAY()),36)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),6))
A10	A10	=INDEX('[Debt Dashboard v0.2.xlsb]REFERENCE'!$I$15:$I$34,2)
D16:F16	D16	=SUM(D3:D15)