Populate summary by sorting data for customer for each sheet alone based on dates

[KalilMe]

Hello,
I would create report for each sheet alone for each customer .
I need macro to sort big data for each sheet (about 11000 rows for each sheet alone)
every customer in column C should merge amount in column J with exclude TOTAL row until doesn't merging twice for each sheet alone, just notice VOUCHER sheet doesn't contain TOTAL row. so when merge amount ,then should search for TOTAL column with exclude TOTAL row if it's existed
merging for each sheet will be based on two condition
first if I write two dates in D5,F5

some data for some sheets

KM.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ITEM	DATE	NAME	INVOICE	GOODS	TYPE	PR	QTY	UNIT	TOTAL
2	1	19/07/2023	CR-1000	STVG-1000	ATR	AM1	MTR	5	25	125
3	2	19/07/2023	CR-1000	STVG-1000	ATR	AM2	PO	4	35	140
4	3	19/07/2023	CR-1000	STVG-1000	ATR	AM1	SO	2	45	90
5	TOTAL									355
6	1	20/07/2023	CR-1001	STVG-1001	ATR	AM1	GR	2	23	46
7	2	20/07/2023	CR-1001	STVG-1001	ATR	AM2	PO	2	35	70
8	TOTAL									116
9	1	21/07/2023	CR-1000	STVG-1000	ATR	AM1	MTR	2	25	50
10	2	21/07/2023	CR-1000	STVG-1000	ATR	AM2	PO	2	35	70
11	3	21/07/2023	CR-1000	STVG-1000	ATR	AM1	SO	2	45	90
12	TOTAL									210
BUYING

KM.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ITEM	DATE	NAME	INVOICE	GOODS	TYPE	PR	QTY	UNIT	TOTAL
2	1	19/07/2023	CR-1000	FRVG-1000	ATR	AM1	GR	55	22	1210
3	2	19/07/2023	CR-1000	FRVG-1000	ATR	AM2	PO	14	33	462
4	3	19/07/2023	CR-1000	FRVG-1000	ATR	AM1	SO	10	44	440
5	TOTAL									2112
6	1	20/07/2023	CR-1001	FRVG-1001	ATR	AM1	GR	10	22	220
7	2	20/07/2023	CR-1001	FRVG-1001	ATR	AM2	PO	10	33	330
8	TOTAL									550
9	1	20/07/2023	CR-1001	FRVG-1002	ATR	AM1	GR	5	22	110
10	2	20/07/2023	CR-1001	FRVG-1002	ATR	AM2	PO	10	30	300
11	TOTAL									410
12	1	21/07/2023	CR-1002	FRVG-1003	ATR	AM3	GR	10	25	250
13	2	21/07/2023	CR-1002	FRVG-1003	ATR	AM4	PO	10	25	250
14	TOTAL									500
SALES

KM.xlsm
	A	B	C	D	E	F	G
1	ITEM	DATE	NAME	VC. NO	DEBIT	CREDIT	TOTAL
2	1	19/07/2023	CR-1000	CR-1001	2,000.00		2,000.00
3	2	20/07/2023	CR-1000	CR-1001	2,000.00		2,000.00
4	3	21/07/2023	CR-1000	CR-1001		2,500.00	-2,500.00
5	4	22/07/2023	CR-1001	CR-1001		1,000.00	-1,000.00
VOUCHER

original

KM.xlsm
	A	B	C	D	E	F
4				FROM DATE		FROM DATE
5
6
7
8	ITEM	NAME	BUYING	SALES	VOUCHER	NET
REPORT

expected

KM.xlsm
	A	B	C	D	E	F
4				FROM DATE		FROM DATE
5				19/07/2023		20/07/2023
6
7
8	ITEM	NAME	BUYING	SALES	VOUCHER	NET
9	1	CR-1000	355.00	2,112.00	4,000.00	-1,888.00
10	2	CR-1001	116.00	960.00		960.00
REPORT

Cell Formulas
Range	Formula
F9:F10	F9	=D9-E9

and if D5,F5 are empty then should populate like this

KM.xlsm
	A	B	C	D	E	F
4				FROM DATE		FROM DATE
5
6
7
8	ITEM	NAME	BUYING	SALES	VOUCHER	NET
9	1	CR-1000	565.00	2,112.00	1,500.00	612.00
10	2	CR-1001	116.00	960.00	-1,000.00	1,960.00
11	3	CR-1002		500.00
REPORT

Cell Formulas
Range	Formula
F9:F10	F9	=D9-E9

every sheet will contains duplicate customer should merge , some customers are not existed in all of sheets .
last thing I will add new sheets before REPORT sheet with the same structure .
I hope my data are clear.

 

[DanteAmor]

Review the following considerations:

1. TOTAL column.

then should search for TOTAL column

The macro considers the last column of data to be the TOTAL column in all sheets. It's right?


2. How is the NET column calculated?

last thing I will add new sheets before REPORT sheet with the same structure .

If you are going to add new sheets, how do you consider the formula to calculate the NET column?


3. The sheets to be considered should be in row 8 of the REPORT sheet.

If you agree with the above considerations. Try the following macro:

Sub Populate_Summary()
  Dim a As Variant, b As Variant, ky As Variant
  Dim shR As Worksheet, sh As Worksheet
  Dim dic1 As Object, dic2 As Object
  Dim s&, lc&, i&, j&, k&, y&
  Dim nRow&, nCol&, rowTot&
  Dim nSheet As String
  Dim froDate As Long, to_Date As Long
  Dim iniDate As Long, finDate As Long
  
  
  Set shR = Sheets("Report")
  Set dic1 = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  
  'Read sheet names from the Report sheet
  lc = shR.Cells(8, Columns.Count).End(1).Column
  For j = 3 To lc - 1
    nSheet = shR.Cells(8, j).Value
    If Evaluate("ISREF('" & nSheet & "'!A1)") Then
      Set sh = Sheets(nSheet)
      rowTot = rowTot + sh.Range("A" & Rows.Count).End(3).Row
      dic2(nSheet) = j
    End If
  Next
  ReDim b(1 To rowTot, 1 To lc)
  froDate = shR.Range("D5").Value
  to_Date = shR.Range("F5").Value
  
  'Read each sheet
  For Each ky In dic2.keys
    Set sh = Sheets(ky)
    lc = sh.Cells(1, Columns.Count).End(1).Column
    a = sh.Range("A1", sh.Cells(sh.Range("A" & Rows.Count).End(3).Row, lc)).Value2
    For i = 2 To UBound(a)
      If UCase(a(i, 1)) <> "TOTAL" Then
      
        If froDate = 0 Then iniDate = a(i, 2) Else iniDate = froDate
        If to_Date = 0 Then finDate = a(i, 2) Else finDate = to_Date
      
        If a(i, 2) >= iniDate And a(i, 2) <= finDate Then
          If Not dic1.exists(a(i, 3)) Then
            y = y + 1
            dic1(a(i, 3)) = y
          End If
          nRow = dic1(a(i, 3))
          nCol = dic2(sh.Name)
          b(nRow, 1) = nRow
          b(nRow, 2) = a(i, 3)
          b(nRow, nCol) = b(nRow, nCol) + a(i, lc)
        End If
      End If
    Next
  Next
  
  shR.Range("A9").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

 

[KalilMe]

The macro considers the last column of data to be the TOTAL column in all sheets. It's right?

Yes.

2. How is the NET column calculated?

as I posted !

If you are going to add new sheets, how do you consider the formula to calculate the NET column?

will not change when add new sheets at all.
thank you

 

[DanteAmor]

2. How is the NET column calculated?
as I posted !

After this line:

b(nRow, nCol) = b(nRow, nCol) + a(i, lc)

Add this line:

b(nRow, UBound(b, 2)) = b(nRow, 4) - b(nRow, 5)

 

[KalilMe]

that's awesome !
many thanks for your help.