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

Hasson

Active Member
Joined
Apr 8, 2021
Messages
406
Office Version
  1. 2016
Platform
  1. Windows
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
EFGHIJK
1ITEMDATEBATCHINV NOQTYPRICEBALANCE
2101/01/2023BTS00INV-00120.00120.002,400.00
3201/01/2023BTS01INV-00150.00110.005,500.00
4301/01/2023BTS02INV-00150.00221.0011,050.00
5401/01/2023BTS03INV-00160.00122.007,320.00
6BALANCE01/01/2023INV-00126,270.00
7103/01/2023BTS01INV-00270.00120.008,400.00
8203/01/2023BTS02INV-00280.00115.009,200.00
9303/01/2023BTS00INV-00260.00112.006,720.00
10BALANCE03/01/2023INV-00224,320.00
11103/01/2023BTS01INV-00380.00122.009,760.00
12204/01/2023BTS02INV-00380.00111.008,880.00
13305/01/2023BTS00INV-00380.00120.009,600.00
14BALANCE06/01/2023INV-00328,240.00
15103/02/2023BTS01INV-004200.00110.0022,000.00
16204/02/2023BTS02INV-004100.00120.0012,000.00
17305/02/2023BTS00INV-004120.0012.001,440.00
18BALANCEINV-00435,440.00
SH1
Cell Formulas
RangeFormula
K15:K17,K11:K13,K7:K9,K2:K5K2=I2*J2
K6K6=SUM(K2:K5)
K10,K18,K14K10=SUM(K7:K9)


MONTHS.xlsm
EFGHIJK
1ITEMDATEBATCHINV NOQTYPRICEBALANCE
2101/01/2023BTS00STT-00112.001,222.0014,664.00
3201/01/2023BTS01STT-00110.00122.001,220.00
4301/01/2023BTS02STT-0015.00244.001,220.00
5401/01/2023BTS03STT-00122.00145.003,190.00
6BALANCE01/01/2023STT-00120,294.00
7103/01/2023BTS01STT-00212.00130.001,560.00
8203/01/2023BTS02STT-00210.00230.002,300.00
9303/01/2023BTS00STT-0025.00150.00750.00
10BALANCE03/01/2023STT-0024,610.00
11103/01/2023BTS01STT-00320.00130.002,600.00
12204/01/2023BTS02STT-00312.00230.002,760.00
13305/01/2023BTS00STT-00322.00150.003,300.00
14BALANCE06/01/2023STT-0038,660.00
15103/02/2023BTS01STT-00320.00130.002,600.00
16204/02/2023BTS02STT-00312.00230.002,760.00
17305/02/2023BTS00STT-00322.00150.003,300.00
18BALANCE06/02/2023STT-0038,660.00
19103/02/2023BTS01STT-003134.00130.0017,420.00
20204/02/2023BTS02STT-00324.00230.005,520.00
21305/02/2023BTS00STT-00334.00150.005,100.00
22BALANCE06/02/2023STT-00328,040.00
MSH
Cell Formulas
RangeFormula
K19:K21,K15:K17,K11:K13,K7:K9,K2:K5K2=I2*J2
K6K6=SUM(K2:K5)
K10,K22,K18,K14K10=SUM(K7:K9)



MONTHS.xlsm
EFGHI
1ITEMDATENAMEVOUCHER NOBALANCE
2111/03/2023HS1VGN 20002,000.00
3212/03/2023HS1VBH6771,800.00
4313/03/2023HS1CVF5001,700.00
ASW



result what I want
MONTHS.xlsm
ABCDEFGHIJKLMN
1ITEMSHEET NAMEJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
21SH178,830.0035,440.00----------
32MSH33,564.0036,700.00----------
43ASW--5,500.00---------
5CAL112,394.0072,140.005,500.000.000.000.000.000.000.000.000.000.00
OUTCOME
Cell Formulas
RangeFormula
C5:N5C5=SUM(C2:C4)

thanks in advance
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
EFGHIJK
1ITEMDATEBATCHINV NOQTYPRICEBALANCE
2101/01/2023BTS00INV-00120.00120.002,400.00
3201/01/2023BTS01INV-00150.00110.005,500.00
4301/01/2023BTS02INV-00150.00221.0011,050.00
5401/01/2023BTS03INV-00160.00122.007,320.00
6BALANCE01/01/2023INV-00126,270.00
7103/01/2023BTS01INV-00270.00120.008,400.00
8203/01/2023BTS02INV-00280.00115.009,200.00
9303/01/2023BTS00INV-00260.00112.006,720.00
10BALANCE03/01/2023INV-00224,320.00
11103/01/2023BTS01INV-00380.00122.009,760.00
12204/01/2023BTS02INV-00380.00111.008,880.00
13305/01/2023BTS00INV-00380.00120.009,600.00
14BALANCE06/01/2023INV-00328,240.00
15103/02/2023BTS01INV-004200.00110.0022,000.00
16204/02/2023BTS02INV-004100.00120.0012,000.00
17305/02/2023BTS00INV-004120.0012.001,440.00
18BALANCEINV-00435,440.00
SH1
Cell Formulas
RangeFormula
K15:K17,K11:K13,K7:K9,K2:K5K2=I2*J2
K6K6=SUM(K2:K5)
K10,K18,K14K10=SUM(K7:K9)


MONTHS.xlsm
EFGHIJK
1ITEMDATEBATCHINV NOQTYPRICEBALANCE
2101/01/2023BTS00STT-00112.001,222.0014,664.00
3201/01/2023BTS01STT-00110.00122.001,220.00
4301/01/2023BTS02STT-0015.00244.001,220.00
5401/01/2023BTS03STT-00122.00145.003,190.00
6BALANCE01/01/2023STT-00120,294.00
7103/01/2023BTS01STT-00212.00130.001,560.00
8203/01/2023BTS02STT-00210.00230.002,300.00
9303/01/2023BTS00STT-0025.00150.00750.00
10BALANCE03/01/2023STT-0024,610.00
11103/01/2023BTS01STT-00320.00130.002,600.00
12204/01/2023BTS02STT-00312.00230.002,760.00
13305/01/2023BTS00STT-00322.00150.003,300.00
14BALANCE06/01/2023STT-0038,660.00
15103/02/2023BTS01STT-00320.00130.002,600.00
16204/02/2023BTS02STT-00312.00230.002,760.00
17305/02/2023BTS00STT-00322.00150.003,300.00
18BALANCE06/02/2023STT-0038,660.00
19103/02/2023BTS01STT-003134.00130.0017,420.00
20204/02/2023BTS02STT-00324.00230.005,520.00
21305/02/2023BTS00STT-00334.00150.005,100.00
22BALANCE06/02/2023STT-00328,040.00
MSH
Cell Formulas
RangeFormula
K19:K21,K15:K17,K11:K13,K7:K9,K2:K5K2=I2*J2
K6K6=SUM(K2:K5)
K10,K22,K18,K14K10=SUM(K7:K9)



MONTHS.xlsm
EFGHI
1ITEMDATENAMEVOUCHER NOBALANCE
2111/03/2023HS1VGN 20002,000.00
3212/03/2023HS1VBH6771,800.00
4313/03/2023HS1CVF5001,700.00
ASW



result what I want
MONTHS.xlsm
ABCDEFGHIJKLMN
1ITEMSHEET NAMEJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
21SH178,830.0035,440.00----------
32MSH33,564.0036,700.00----------
43ASW--5,500.00---------
5CAL112,394.0072,140.005,500.000.000.000.000.000.000.000.000.000.00
OUTCOME
Cell Formulas
RangeFormula
C5:N5C5=SUM(C2:C4)

thanks in advance
Popup to Notify of Expiring - Expired Dates.xlsm
ABCDEFGHIJKLMN
1ItemSheet NameJanFebMarAprMayJunJulAugSepOctNovDec
21SH1£78,830£34,000         £1,440
32MSH£78,830£35,440          
43ASW£2,400£5,500£11,050         
5CAL
Outcome
Cell Formulas
RangeFormula
D1:N1D1=DATE(YEAR(C1),MONTH(C1)+1,1)
C2:N2C2=SUMIFS('SH1'!$G:$G,'SH1'!$B:$B,">="&Outcome!C$1,'SH1'!$B:$B,"<="&EOMONTH(C$1,0),'SH1'!$A:$A,"<>"&"BALANCE")
C3:N3C3=SUMIFS(MSN!$G:$G,MSN!$B:$B,">="&Outcome!C$1,MSN!$B:$B,"<="&EOMONTH(C$1,0),MSN!$A:$A,"<>"&"BALANCE")
C4:N4C4=SUMIFS(ASW!$E:$E,ASW!$B:$B,">=" & Outcome!C$1,ASW!$B:$B, "<=" & EOMONTH(Outcome!C$1,0))
 
Upvote 0
Popup to Notify of Expiring - Expired Dates.xlsm
ABCDEFGHIJKLMN
1ItemSheet NameJanFebMarAprMayJunJulAugSepOctNovDec
21SH1£78,830£34,000         £1,440
32MSH£78,830£35,440          
43ASW£2,400£5,500£11,050         
5CAL
Outcome
Cell Formulas
RangeFormula
D1:N1D1=DATE(YEAR(C1),MONTH(C1)+1,1)
C2:N2C2=SUMIFS('SH1'!$G:$G,'SH1'!$B:$B,">="&Outcome!C$1,'SH1'!$B:$B,"<="&EOMONTH(C$1,0),'SH1'!$A:$A,"<>"&"BALANCE")
C3:N3C3=SUMIFS(MSN!$G:$G,MSN!$B:$B,">="&Outcome!C$1,MSN!$B:$B,"<="&EOMONTH(C$1,0),MSN!$A:$A,"<>"&"BALANCE")
C4:N4C4=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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Are you expecting some code to create the new row on the Outcome worksheet when you add a new sheet containing data?
 
Upvote 0

Forum statistics

Threads
1,224,814
Messages
6,181,125
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top