VBA: Add blank rows and formula after data of a whole month

[jvandeliefvoort]

Hi all,

I have a question about a data sheet I have to edit. For a company, I receive data from a machine each day. That data has to be calculated to other values, and after each month I need the totals of these data. I want to create a VBA-code, so that each time they upload the data, I can put in the code and it will automatically calculate the values and the sum after each month. This has to be done per machine in a sheet (so I have multiple sheets per Excel-document of different machines who all have different data).

Since I cannot/may not share the data, I made a sheet with fictive data to try. I really have no idea how to start with the code.

So: I have raw data (value 1-value 4, first table) of each day of the month. I need to calculate data from this (value 5, 6, 7) and I need a sum/mean of these values (second table) with two blank rows in between.

I hope this is a clear question, and I created a XLB22 add-in. Thanks in advance.

Fictive "raw" data

Test Inv.xlsx
	A	B	C	D	E
1	Date	Value 1	Value 2	Value 3	Value 4
2	11-10-2021 00:00	564845	496973	215486	399187
3	10-10-2021 00:00	534654	470410	541354	377851
4	9-10-2021 00:00	454845	400191	545122	321448
5	8-10-2021 00:00	241613	212581	154565	170753
6	7-10-2021 00:00	214526	188749	215553	151610
7	6-10-2021 00:00	545612	480051	545452	385595
8	5-10-2021 00:00	654848	576161	153315	462794
9	4-10-2021 00:00	989563	870657	215345	699344
10	3-10-2021 00:00	325485	286375	215656	230027
11	2-10-2021 00:00	565495	497545	844664	399647
12	1-10-2021 00:00	456123	401315	546545	322351
13	30-9-2021 00:00	845612	845615	248426	985642
14	29-9-2021 00:00	545643	546225	154564	235133
15	28-9-2021 00:00	545621	584456	321521	658412
16	27-9-2021 00:00	656412	564865	212021	231515
17	26-9-2021 00:00	214132	564546	212312	215315
Sheet1

Disired end result

Test Inv.xlsx
	A	B	C	D	E	F	G	H
1	Date	Value 1	Value 2	Value 3	Value 4	Value 5	Value 6	Value 7
2	11-10-2021 00:00	564845	496973	215486	399187	15.69	19.79	0.79
3	10-10-2021 00:00	534654	470410	541354	377851	14.85	28.10	0.53
4	9-10-2021 00:00	454845	400191	545122	321448	12.63	26.26	0.48
5	8-10-2021 00:00	241613	212581	154565	170753	6.71	10.20	0.66
6	7-10-2021 00:00	214526	188749	215553	151610	5.96	11.23	0.53
7	6-10-2021 00:00	545612	480051	545452	385595	15.16	28.49	0.53
8	5-10-2021 00:00	654848	576161	153315	462794	18.19	20.26	0.90
9	4-10-2021 00:00	989563	870657	215345	699344	27.49	30.17	0.91
10	3-10-2021 00:00	325485	286375	215656	230027	9.04	13.95	0.65
11	2-10-2021 00:00	565495	497545	844664	399647	15.71	37.28	0.42
12	1-10-2021 00:00	456123	401315	546545	322351	12.67	26.33	0.48
13
14	oct 2021					154.10	252.06	0.61
15
16	30-9-2021 00:00	845612	845615	248426	985642	23.49	30.39	0.77
17	29-9-2021 00:00	545643	546225	154564	235133	15.16	19.47	0.78
18	28-9-2021 00:00	545621	584456	321521	658412	15.16	25.17	0.60
19	27-9-2021 00:00	656412	564865	212021	231515	18.23	21.58	0.84
20	26-9-2021 00:00	214132	564546	212312	215315	5.95	21.58	0.28
Sheet1

Cell Formulas
Range	Formula
F2:F12	F2	=[@[Value 1]]/36000
G2:G12	G2	=SUM(C2,D2)/36000
H2:H12,H14	H2	=[@[Value 5]]/[@[Value 6]]
F14:G14	F14	=SUM(F2:F12)
F16:F20	F16	=B16/36000
G16:G20	G16	=SUM(C16:D16)/36000
H16:H20	H16	=F16/G16

 

[jvandeliefvoort]

Anyone who can help?

 

[BSALV]

with a pivottable, you can do most of the job, and the VBA, that's a few lines, but that's almost 1 on 1, simple. Copying the data to my sheet went wrong for values 2 and 4, so don't look at those.

Map1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Date	Value 1	Value 2	Value 3	Value 4	Value 5	Value 6	Value 7			Rijlabels	Som van Value 1	Som van Value 2	Som van Value 3	Som van Value 4	Som van Value 5	Som van Value 6	Som van 5/6
2	11/10/21	564845	4.969.732.248	215486	3.991.872.584	15,69013889	138054,1037	0,000113652			2021	8355029	3,36806E+11	5341901	2,70535E+11	232,0841389	9355881,32	2,48062E-05
3	10/10/21	534654	47.040.997.536	541354	37.785.067.488	14,8515	1306709,414	1,13656E-05			sep	2807420	3105707	1148844	2326017	77,98388889	118,1819722	0,659862815
4	9/10/21	454845	4.001.908.248	545122	3.214.480.584	12,63458333	111179,2603	0,000113642			26/sep	214132	564546	212312	215315	5,948111111	21,57938889	0,275638534
5	8/10/21	241613	21.258.078.192	154565	17.075.273.936	6,711472222	590506,4655	1,13656E-05			27/sep	656412	564865	212021	231515	18,23366667	21,58016667	0,844927055
6	7/10/21	214526	18.874.855.584	215553	15.160.981.472	5,959055556	524307,5316	1,13656E-05			28/sep	545621	584456	321521	658412	15,15613889	25,16602778	0,602245973
7	6/10/21	545612	48.005.126.208	545452	38.559.491.264	15,15588889	1333490,879	1,13656E-05			29/sep	545643	546225	154564	235133	15,15675	19,46636111	0,778612393
8	5/10/21	654848	57.616.146.432	153315	46.279.417.856	18,19022222	1600452,771	1,13657E-05			30/sep	845612	845615	248426	985642	23,48922222	30,39002778	0,772925329
9	4/10/21	989563	87.065.710.992	215345	69.934.396.336	27,48786111	2418497,954	1,13657E-05
10	3/10/21	325485	2.863.747.224	215656	2.300.267.592	9,04125	79554,52444	0,000113648			okt	5547609	3,36803E+11	4193057	2,70533E+11	154,10025	9355763,138	1,64712E-05
11	2/10/21	565495	4.975.451.208	844664	3.996.466.264	15,70819444	138230,4409	0,000113638			1/okt	456123	40131526032	546545	32235124656	12,67008333	1114779,794	1,13655E-05
12	1/10/21	456123	40.131.526.032	546545	32.235.124.656	12,67008333	1114779,794	1,13655E-05			2/okt	565495	4975451208	844664	3996466264	15,70819444	138230,4409	0,000113638
13	30/09/21	845612	845615	248426	985642	23,48922222	30,39002778	0,772925329			3/okt	325485	2863747224	215656	2300267592	9,04125	79554,52444	0,000113648
14	29/09/21	545643	546225	154564	235133	15,15675	19,46636111	0,778612393			4/okt	989563	87065710992	215345	69934396336	27,48786111	2418497,954	1,13657E-05
15	28/09/21	545621	584456	321521	658412	15,15613889	25,16602778	0,602245973			5/okt	654848	57616146432	153315	46279417856	18,19022222	1600452,771	1,13657E-05
16	27/09/21	656412	564865	212021	231515	18,23366667	21,58016667	0,844927055			6/okt	545612	48005126208	545452	38559491264	15,15588889	1333490,879	1,13656E-05
17	26/09/21	214132	564546	212312	215315	5,948111111	21,57938889	0,275638534			7/okt	214526	18874855584	215553	15160981472	5,959055556	524307,5316	1,13656E-05
18											8/okt	241613	21258078192	154565	17075273936	6,711472222	590506,4655	1,13656E-05
19											9/okt	454845	4001908248	545122	3214480584	12,63458333	111179,2603	0,000113642
20											10/okt	534654	47040997536	541354	37785067488	14,8515	1306709,414	1,13656E-05
21											11/okt	564845	4969732248	215486	3991872584	15,69013889	138054,1037	0,000113652
22
23											Eindtotaal	8355029	3,36806E+11	5341901	2,70535E+11	232,0841389	9355881,32	2,48062E-05
24
Blad4

Cell Formulas
Range	Formula
F2:F17	F2	=[@[Value 1]]/36000
G2:G17	G2	=SUM(C2,D2)/36000
H2:H17	H2	=[@[Value 5]]/[@[Value 6]]