search for macro to sum amount for each sheet based on month

[Hasson]

Hello
I need macro to deal with about 3500 rows for each sheet , every time I add new sheets and data before OUTCOME sheet
I want sum whole of BALANCE column for each sheet based on month , but if any sheet contains BALANCE word in row Except header in row1 should exclude it until avoid sum amount twice .
so the result should be as in OUTCOME sheet with populate sheets names in column B and insert CAL row to sum all of sheets for each month .

MONTHS.xlsm
	E	F	G	H	I	J	K
1	ITEM	DATE	BATCH	INV NO	QTY	PRICE	BALANCE
2	1	01/01/2023	BTS00	INV-001	20.00	120.00	2,400.00
3	2	01/01/2023	BTS01	INV-001	50.00	110.00	5,500.00
4	3	01/01/2023	BTS02	INV-001	50.00	221.00	11,050.00
5	4	01/01/2023	BTS03	INV-001	60.00	122.00	7,320.00
6	BALANCE	01/01/2023		INV-001			26,270.00
7	1	03/01/2023	BTS01	INV-002	70.00	120.00	8,400.00
8	2	03/01/2023	BTS02	INV-002	80.00	115.00	9,200.00
9	3	03/01/2023	BTS00	INV-002	60.00	112.00	6,720.00
10	BALANCE	03/01/2023		INV-002			24,320.00
11	1	03/01/2023	BTS01	INV-003	80.00	122.00	9,760.00
12	2	04/01/2023	BTS02	INV-003	80.00	111.00	8,880.00
13	3	05/01/2023	BTS00	INV-003	80.00	120.00	9,600.00
14	BALANCE	06/01/2023		INV-003			28,240.00
15	1	03/02/2023	BTS01	INV-004	200.00	110.00	22,000.00
16	2	04/02/2023	BTS02	INV-004	100.00	120.00	12,000.00
17	3	05/02/2023	BTS00	INV-004	120.00	12.00	1,440.00
18	BALANCE			INV-004			35,440.00
SH1

Cell Formulas
Range	Formula
K15:K17,K11:K13,K7:K9,K2:K5	K2	=I2*J2
K6	K6	=SUM(K2:K5)
K10,K18,K14	K10	=SUM(K7:K9)

MONTHS.xlsm
	E	F	G	H	I	J	K
1	ITEM	DATE	BATCH	INV NO	QTY	PRICE	BALANCE
2	1	01/01/2023	BTS00	STT-001	12.00	1,222.00	14,664.00
3	2	01/01/2023	BTS01	STT-001	10.00	122.00	1,220.00
4	3	01/01/2023	BTS02	STT-001	5.00	244.00	1,220.00
5	4	01/01/2023	BTS03	STT-001	22.00	145.00	3,190.00
6	BALANCE	01/01/2023		STT-001			20,294.00
7	1	03/01/2023	BTS01	STT-002	12.00	130.00	1,560.00
8	2	03/01/2023	BTS02	STT-002	10.00	230.00	2,300.00
9	3	03/01/2023	BTS00	STT-002	5.00	150.00	750.00
10	BALANCE	03/01/2023		STT-002			4,610.00
11	1	03/01/2023	BTS01	STT-003	20.00	130.00	2,600.00
12	2	04/01/2023	BTS02	STT-003	12.00	230.00	2,760.00
13	3	05/01/2023	BTS00	STT-003	22.00	150.00	3,300.00
14	BALANCE	06/01/2023		STT-003			8,660.00
15	1	03/02/2023	BTS01	STT-003	20.00	130.00	2,600.00
16	2	04/02/2023	BTS02	STT-003	12.00	230.00	2,760.00
17	3	05/02/2023	BTS00	STT-003	22.00	150.00	3,300.00
18	BALANCE	06/02/2023		STT-003			8,660.00
19	1	03/02/2023	BTS01	STT-003	134.00	130.00	17,420.00
20	2	04/02/2023	BTS02	STT-003	24.00	230.00	5,520.00
21	3	05/02/2023	BTS00	STT-003	34.00	150.00	5,100.00
22	BALANCE	06/02/2023		STT-003			28,040.00
MSH

Cell Formulas
Range	Formula
K19:K21,K15:K17,K11:K13,K7:K9,K2:K5	K2	=I2*J2
K6	K6	=SUM(K2:K5)
K10,K22,K18,K14	K10	=SUM(K7:K9)

MONTHS.xlsm
	E	F	G	H	I
1	ITEM	DATE	NAME	VOUCHER NO	BALANCE
2	1	11/03/2023	HS1	VGN 2000	2,000.00
3	2	12/03/2023	HS1	VBH677	1,800.00
4	3	13/03/2023	HS1	CVF500	1,700.00
ASW

result what I want

MONTHS.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ITEM	SHEET NAME	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
2	1	SH1	78,830.00	35,440.00	-	-	-	-	-	-	-	-	-	-
3	2	MSH	33,564.00	36,700.00	-	-	-	-	-	-	-	-	-	-
4	3	ASW	-	-	5,500.00	-	-	-	-	-	-	-	-	-
5	CAL		112,394.00	72,140.00	5,500.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
OUTCOME

Cell Formulas
Range	Formula
C5:N5	C5	=SUM(C2:C4)

thanks in advance

 

[Herakles]

Hello
I need macro to deal with about 3500 rows for each sheet , every time I add new sheets and data before OUTCOME sheet
I want sum whole of BALANCE column for each sheet based on month , but if any sheet contains BALANCE word in row Except header in row1 should exclude it until avoid sum amount twice .
so the result should be as in OUTCOME sheet with populate sheets names in column B and insert CAL row to sum all of sheets for each month .

MONTHS.xlsm
	E	F	G	H	I	J	K
1	ITEM	DATE	BATCH	INV NO	QTY	PRICE	BALANCE
2	1	01/01/2023	BTS00	INV-001	20.00	120.00	2,400.00
3	2	01/01/2023	BTS01	INV-001	50.00	110.00	5,500.00
4	3	01/01/2023	BTS02	INV-001	50.00	221.00	11,050.00
5	4	01/01/2023	BTS03	INV-001	60.00	122.00	7,320.00
6	BALANCE	01/01/2023		INV-001			26,270.00
7	1	03/01/2023	BTS01	INV-002	70.00	120.00	8,400.00
8	2	03/01/2023	BTS02	INV-002	80.00	115.00	9,200.00
9	3	03/01/2023	BTS00	INV-002	60.00	112.00	6,720.00
10	BALANCE	03/01/2023		INV-002			24,320.00
11	1	03/01/2023	BTS01	INV-003	80.00	122.00	9,760.00
12	2	04/01/2023	BTS02	INV-003	80.00	111.00	8,880.00
13	3	05/01/2023	BTS00	INV-003	80.00	120.00	9,600.00
14	BALANCE	06/01/2023		INV-003			28,240.00
15	1	03/02/2023	BTS01	INV-004	200.00	110.00	22,000.00
16	2	04/02/2023	BTS02	INV-004	100.00	120.00	12,000.00
17	3	05/02/2023	BTS00	INV-004	120.00	12.00	1,440.00
18	BALANCE			INV-004			35,440.00
SH1

Cell Formulas
Range	Formula
K15:K17,K11:K13,K7:K9,K2:K5	K2	=I2*J2
K6	K6	=SUM(K2:K5)
K10,K18,K14	K10	=SUM(K7:K9)

MONTHS.xlsm
	E	F	G	H	I	J	K
1	ITEM	DATE	BATCH	INV NO	QTY	PRICE	BALANCE
2	1	01/01/2023	BTS00	STT-001	12.00	1,222.00	14,664.00
3	2	01/01/2023	BTS01	STT-001	10.00	122.00	1,220.00
4	3	01/01/2023	BTS02	STT-001	5.00	244.00	1,220.00
5	4	01/01/2023	BTS03	STT-001	22.00	145.00	3,190.00
6	BALANCE	01/01/2023		STT-001			20,294.00
7	1	03/01/2023	BTS01	STT-002	12.00	130.00	1,560.00
8	2	03/01/2023	BTS02	STT-002	10.00	230.00	2,300.00
9	3	03/01/2023	BTS00	STT-002	5.00	150.00	750.00
10	BALANCE	03/01/2023		STT-002			4,610.00
11	1	03/01/2023	BTS01	STT-003	20.00	130.00	2,600.00
12	2	04/01/2023	BTS02	STT-003	12.00	230.00	2,760.00
13	3	05/01/2023	BTS00	STT-003	22.00	150.00	3,300.00
14	BALANCE	06/01/2023		STT-003			8,660.00
15	1	03/02/2023	BTS01	STT-003	20.00	130.00	2,600.00
16	2	04/02/2023	BTS02	STT-003	12.00	230.00	2,760.00
17	3	05/02/2023	BTS00	STT-003	22.00	150.00	3,300.00
18	BALANCE	06/02/2023		STT-003			8,660.00
19	1	03/02/2023	BTS01	STT-003	134.00	130.00	17,420.00
20	2	04/02/2023	BTS02	STT-003	24.00	230.00	5,520.00
21	3	05/02/2023	BTS00	STT-003	34.00	150.00	5,100.00
22	BALANCE	06/02/2023		STT-003			28,040.00
MSH

Cell Formulas
Range	Formula
K19:K21,K15:K17,K11:K13,K7:K9,K2:K5	K2	=I2*J2
K6	K6	=SUM(K2:K5)
K10,K22,K18,K14	K10	=SUM(K7:K9)

MONTHS.xlsm
	E	F	G	H	I
1	ITEM	DATE	NAME	VOUCHER NO	BALANCE
2	1	11/03/2023	HS1	VGN 2000	2,000.00
3	2	12/03/2023	HS1	VBH677	1,800.00
4	3	13/03/2023	HS1	CVF500	1,700.00
ASW

result what I want

MONTHS.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ITEM	SHEET NAME	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
2	1	SH1	78,830.00	35,440.00	-	-	-	-	-	-	-	-	-	-
3	2	MSH	33,564.00	36,700.00	-	-	-	-	-	-	-	-	-	-
4	3	ASW	-	-	5,500.00	-	-	-	-	-	-	-	-	-
5	CAL		112,394.00	72,140.00	5,500.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
OUTCOME

Cell Formulas
Range	Formula
C5:N5	C5	=SUM(C2:C4)

thanks in advance

Popup to Notify of Expiring - Expired Dates.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Item	Sheet Name	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	1	SH1	£78,830	£34,000										£1,440
3	2	MSH	£78,830	£35,440
4	3	ASW	£2,400	£5,500	£11,050
5	CAL
Outcome

Cell Formulas
Range	Formula
D1:N1	D1	=DATE(YEAR(C1),MONTH(C1)+1,1)
C2:N2	C2	=SUMIFS('SH1'!$G:$G,'SH1'!$B:$B,">="&Outcome!C$1,'SH1'!$B:$B,"<="&EOMONTH(C$1,0),'SH1'!$A:$A,"<>"&"BALANCE")
C3:N3	C3	=SUMIFS(MSN!$G:$G,MSN!$B:$B,">="&Outcome!C$1,MSN!$B:$B,"<="&EOMONTH(C$1,0),MSN!$A:$A,"<>"&"BALANCE")
C4:N4	C4	=SUMIFS(ASW!$E:$E,ASW!$B:$B,">=" & Outcome!C$1,ASW!$B:$B, "<=" & EOMONTH(Outcome!C$1,0))

 

[Herakles]

Popup to Notify of Expiring - Expired Dates.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Item	Sheet Name	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	1	SH1	£78,830	£34,000										£1,440
3	2	MSH	£78,830	£35,440
4	3	ASW	£2,400	£5,500	£11,050
5	CAL
Outcome

Cell Formulas
Range	Formula
D1:N1	D1	=DATE(YEAR(C1),MONTH(C1)+1,1)
C2:N2	C2	=SUMIFS('SH1'!$G:$G,'SH1'!$B:$B,">="&Outcome!C$1,'SH1'!$B:$B,"<="&EOMONTH(C$1,0),'SH1'!$A:$A,"<>"&"BALANCE")
C3:N3	C3	=SUMIFS(MSN!$G:$G,MSN!$B:$B,">="&Outcome!C$1,MSN!$B:$B,"<="&EOMONTH(C$1,0),MSN!$A:$A,"<>"&"BALANCE")
C4:N4	C4	=SUMIFS(ASW!$E:$E,ASW!$B:$B,">=" & Outcome!C$1,ASW!$B:$B, "<=" & EOMONTH(Outcome!C$1,0))

Cell C1 is 01/01/2023 or January 1st in whatever year.

 

[Hasson]

May you do that by macro please?

 

[Herakles]

May you do that by macro please?

What would be the advantage of setting up a worksheet such as these using VBA?

 

[Hasson]

as I said

I need macro to deal with about 3500 rows for each sheet , every time I add new sheets and data before OUTCOME sheet

based on this will add the formula based on add new sheets every time !
is it practical?

 

[Herakles]

as I said

based on this will add the formula based on add new sheets every time !
is it practical?

The number of rows is irrelevant. All you need to do is copy columns C to N when setting up for a new worksheet.

You will need to consider the two different layouts of worksheet but you have the template now.

 

[Hasson]

I'm talking about when add new sheets . based on your formula you indicate sheet name reference
so new sheet need the same formula with change sheet name

 

[Herakles]

Are you expecting some code to create the new row on the Outcome worksheet when you add a new sheet containing data?

 

[Hasson]

yes should be.