match based on headers and cell and sheet name to summing amounts

[tubrak]

Hello
I need populating sheets names in column A and I will write in cell C1 the month and headers B2:E2 across sheets
so if I write month in C1 JAN , then should populate list of sheets names in column A and match headers and should sum the amounts for each sheet under header is matched based on month or sum the amounts for each sheet under header is matched for whole months when C1 is empty and the last column BALANCE should calculate as I put it , but I don't need any formulas , the same thing should populate TOTAL values for each column and will change in location based on the last row contains sheet name.
every time I will add new sheets and with the same structure and will increase sheets in output sheet .
if can do by vba with deal big data for all of sheets will be a great.
here is data in multiple sheets

assss.xlsm
	A	B	C	D	E
1	DATE	INVOICE NUMBER	DEBIT CU	CREDIT CU	BALANCE
2	01/01/2023	INV10022	10000		10000
3	02/01/2023	INV10023	15000		25000
4	03/01/2023	INV10024	20000	2000	43000
5	04/01/2023	INV10025	25000	2000	66000
6	05/02/2023	INV10026	2000		68000
7	06/02/2023	INV10027	10000		78000
8	07/02/2023	CASH		1000	77000
9	08/02/2023	BANK		1000	76000
10	09/02/2023	CASH		8000	68000
11	10/02/2023	CASH		1200	66800
12	11/02/2023	INV10028	234	1000	66034
13	12/02/2023	INV10029	12300		78334
AA

Cell Formulas
Range	Formula
E2	E2	=C2
E3:E13	E3	=E2+C3-D3

assss.xlsm
	A	B	C	D	E
1	DATE	INVOICE NUMBER	DEBIT CU	CREDIT CU	BALANCE
2	01/01/2023	INV1001	10000		10000
3	02/01/2023	INV1011	15000		25000
4	03/01/2023	CASH		2000	23000
5	04/01/2023	CASH		2000	21000
6	05/02/2023	INV1002	2000		23000
7	06/02/2023	INV1003	10000		33000
8	07/02/2023	BANK		1000	32000
9	08/02/2023	BANK		1000	31000
10	09/02/2023	BANK		8000	23000
11	10/02/2023	BANK		1200	21800
12	11/02/2023	INV1008	1200	1000	22000
13	12/02/2023	INV1009			22000
AS

Cell Formulas
Range	Formula
E2	E2	=C2
E3:E13	E3	=E2+C3-D3

assss.xlsm
	A	B	C	D	E
1	DATE	INVOICE NUMBER	CREDIT CL	DEBIT CL	BALANCE
2	01/01/2023	INV100	20000		20000
3	02/01/2023	INV101	20000		40000
4	03/01/2023	BANK		2000	38000
5	04/01/2023	BANK		2000	36000
6	05/02/2023	BANK		1000	35000
7	06/02/2023	BANK		2000	33000
8	07/02/2023	BANK		12000	21000
9	08/02/2023	BANK		12000	9000
10	09/02/2023	BANK		1000	8000
11	10/02/2023	BANK		200	7800
12	11/02/2023	BANK		100	7700
13	12/02/2023	BANK		120	7580
ATM

Cell Formulas
Range	Formula
E2	E2	=C2
E3:E13	E3	=E2+C3-D3

assss.xlsm
	A	B	C	D	E
1	DATE	INVOICE NUMBER	CREDIT	DEBIT CL	BALANCE
2	01/01/2023	INV100	10000		10000
3	02/01/2023	INV101	20000		30000
4	03/01/2023	CASH	50000	2000	78000
5	04/01/2023	CASH		2000	76000
6	05/02/2023	CASH		1200	74800
7	06/02/2023	CASH		120	74680
8	07/02/2023	CASH		200	74480
9	08/02/2023	CASH		12000	62480
10	09/02/2023	CASH		1000	61480
11	10/02/2023	CASH		200	61280
12	11/02/2023	CASH		100	61180
13	12/02/2023	CASH		120	61060
AB

Cell Formulas
Range	Formula
E2	E2	=C2
E3:E13	E3	=E2+C3-D3

before

assss.xlsm
	A	B	C	D	E	F
1
2	sheets names	DEBIT CU	CREDIT CU	CREDIT CL	DEBIT CL	BALANCE
3
4
5
6
OUTPUT

after based on cell1 is month

assss.xlsm
	A	B	C	D	E	F
1			JAN
2	sheets names	DEBIT CU	CREDIT CU	CREDIT CL	DEBIT CL	BALANCE
3	AA	70000	4000			4000
4	AS	25000	4000			8000
5	ATM			40000	4000	4000
6	AB			80000	4000	0
7	TOTAL	95000	8000	120000	8000	0
OUTPUT

Cell Formulas
Range	Formula
F3,F7	F3	=C3-E3
F4:F6	F4	=F3+C4-E4
B7:E7	B7	=SUM(B3:B6)

after based on C1 is empty should sum whole of months

assss.xlsm
	A	B	C	D	E	F
1
2	sheets names	DEBIT CU	CREDIT CU	CREDIT CL	DEBIT CL	BALANCE
3	AA	94534	16200			16200
4	AS	38200	16200			32400
5	ATM			40000	32420	-20
6	AB			80000	18940	-18960
7	TOTAL	132734	32400	120000	51360	-18960
OUTPUT

Cell Formulas
Range	Formula
F3,F7	F3	=C3-E3
F4:F6	F4	=F3+C4-E4
B7:E7	B7	=SUM(B3:B6)

 

[DanteAmor]

Hi @tubrak , I hope you are well.

If you still need the macro, try the next one.

I assume that in cell C1 of the "Output" sheet you have the text "JAN" or the text of the first 3 letters of the month.
If you don't have a text and you have a date, then you should change this line in the macro:

mnt = LCase(shO.Range("C1").Value)

By this line:

mnt = LCase(Format(shO.Range("C1").Value, "mmm"))

Sub match_based_on_headers()
  Dim dic As Object
  Dim sh As Worksheet, shO As Worksheet
  Dim a() As Variant, b As Variant
  Dim i As Long, k As Long, col As Long, lr As Long
  Dim c As Range
  Dim mnt As String
  Dim ant As Double
  
  Application.ScreenUpdating = False
  
  Set shO = Sheets("Output")
  Set dic = CreateObject("Scripting.Dictionary")
  shO.Range("A3:F" & Rows.Count).ClearContents
  shO.Range("A3:F" & Rows.Count).Interior.ColorIndex = xlNone
  mnt = LCase(shO.Range("C1").Value)
  
  If mnt = "" Then k = 2 Else k = 0
  ReDim b(1 To Sheets.Count, 1 To 6)
  
  For Each sh In Sheets
    If sh.Name <> shO.Name Then
      col = 0
      If Trim(sh.Range("C1").Value) = "DEBIT CU" Or Trim(sh.Range("D1").Value) = "CREDIT CU" Then col = 2
      If Trim(sh.Range("C1").Value) = "CREDIT CL" Or Trim(sh.Range("D1").Value) = "DEBIT CL" Then col = 4
      
      If col <> 0 Then
        If mnt = "" Then
          'sum whole months
          k = k + 1
          shO.Cells(k, "A").Value = sh.Name
          shO.Cells(k, col).Value = WorksheetFunction.Sum(sh.Range("C:C"))
          shO.Cells(k, col + 1).Value = WorksheetFunction.Sum(sh.Range("D:D"))
          shO.Cells(k, "F").Value = Val(shO.Cells(k - 1, "F").Value) + shO.Cells(k, "C").Value - shO.Cells(k, "E").Value
        Else
          'sum by month
          Erase a
          a = sh.Range("A1", sh.Range("D" & Rows.Count).End(3)).Value
          k = k + 1
          For i = 2 To UBound(a, 1)
            If Format(a(i, 1), "mmm") = mnt Then
              b(k, col) = b(k, col) + a(i, 3)
              b(k, col + 1) = b(k, col + 1) + a(i, 4)
            End If
          Next
          b(k, 1) = sh.Name
          If k = 1 Then ant = 0 Else ant = b(k - 1, 6)
          b(k, 6) = ant + b(k, 3) - b(k, 5)
        End If
      End If
    End If
  Next
  
  If mnt <> "" Then
    shO.Range("A3").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  End If
  
  'populate TOTAL value
  lr = shO.Range("A" & Rows.Count).End(3).Row
  With shO.Range("B" & lr + 1 & ":E" & lr + 1)
    .Formula = "=SUM(B3:B" & lr & ")"
    .Value = .Value
  End With
  With shO.Range("A" & lr + 1)
    .Value = "TOTAL"
    .Interior.Color = vbYellow
    .Font.Bold = True
    .Offset(0, 5).Value = shO.Range("C" & lr + 1).Value - shO.Range("E" & lr + 1).Value
  End With
    
  Application.ScreenUpdating = False
End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

 

[tubrak]

Hi Dante,
after about 25 days I don't expect somebody help me after all of this period , indeed I lost hope to anybody answers me .
I see your macro works perfectly as I want it .
many thanks for your help.

 

[DanteAmor]

Glad to know it works for you.
I also appreciate your effort to put a pretty clear example.
I hope it was worth the wait time.