transpose data int listbox on userform based on two dates or without dates

[Alaa mg]

Hello
I want merging amounts for each part item in column B for each sheet , when matching with column B for the each sheet . merging of the amounts should be for columns DEBIT or CREDIT and populate the names based on sheets names based on two condition . if the cells textbox1,textbox2are empty then will brings the names and merge amounts as pic1 in listbox1 and if there are two dates in textbox1,textbox2 then should brings the names and merging amounts within two dates as in pic2 .

every time add new sheets and new data or change data are already existed and also could be the others sheets are empty should ignore it until doesn't happen error.

ORIGINAL.xlsm
	A	B	C	D	E
2	01/01/2023	SALES INVOICE NN 899	2,000.00		2,000.00
3	01/01/2023	SALES INVOICE NN 900	2,200.00		4,200.00
4	01/01/2023	SALES INVOICE NN 901	1,400.00		5,600.00
5	01/01/2023	SALES INVOICE NN 902	1,300.00		6,900.00
6	05/01/2023	SALES INVOICE NN 903	1,200.00		8,100.00
7	06/01/2023	SALES INVOICE NN 904	1,100.00		9,200.00
8	07/01/2023	SALES INVOICE NN 905	1,000.00		10,200.00
9	08/01/2023	SALES INVOICE NN 906	1,500.00		11,700.00
10	09/01/2023	SALES INVOICE NN 907	1,600.00		13,300.00
11	10/01/2023	SALES INVOICE NN 908	1,800.00		15,100.00
12	11/01/2023	CASH VOUCHER NO VM6000		5,000.00	10,100.00
13	12/01/2023	SALES INVOICE NN 909	6,000.00		16,100.00
14	12/01/2023	CASH VOUCHER NO VM6001		7,000.00	9,100.00
15	12/01/2023	CASH VOUCHER NO VM6002		6,500.00	2,600.00
16	12/01/2023	CASH VOUCHER NO VM6003		1,200.00	1,400.00
17	12/01/2023	CASH VOUCHER NO VM6004		230.00	1,170.00
18	12/01/2023	SALES INVOICE NN 910	4,500.00		5,670.00
19	12/01/2023	CASH VOUCHER NO VM6005		700.00	4,970.00
20	12/01/2023	CASH VOUCHER NO VM6006		1,000.00	3,970.00
21	12/01/2023	SALES INVOICE NN 911	3,000.00		6,970.00
22	12/01/2023	SALES INVOICE NN 912	60,000.00		66,970.00
23	12/01/2023	PURCHASE INVOICE PP 9000		6,000.00	60,970.00
24	12/01/2023	PURCHASE INVOICE PP 9001		5,000.00	55,970.00
25	12/01/2023	PURCHASE INVOICE PP 9002		1,200.00	54,770.00
26	12/01/2023	PAID VOUCHERVBG-800	4,000.00		58,770.00
27	26/01/2023	PAID VOUCHERVBG-801	5,600.00		64,370.00
28	27/01/2023	PURCHASE INVOICE PP 9003		5,600.00	58,770.00
29	28/01/2023	PURCHASE INVOICE PP 9004		3,400.00	55,370.00
30	29/01/2023	PURCHASE INVOICE PP 9005		120.00	55,250.00
31	30/01/2023	PURCHASE INVOICE PP 9006		300.00	54,950.00
32	31/01/2023	PURCHASE INVOICE PP 9007		500.00	54,450.00
33	01/02/2023	PURCHASE INVOICE PP 9008		3,400.00	51,050.00
34	02/02/2023	PURCHASE INVOICE PP 9009		6,000.00	45,050.00
35	03/02/2023	PURCHASE INVOICE PP 9010		7,000.00	38,050.00
36	04/02/2023	PURCHASE INVOICE PP 9011		8,000.00	30,050.00
37	05/02/2023	PURCHASE INVOICE PP 9012		9,000.00	21,050.00
38	06/02/2023	PAID VOUCHERVBG-802	5,000.00		26,050.00
39	07/02/2023	PURCHASE INVOICE PP 9013		7,000.00	19,050.00
40	08/02/2023	PURCHASE INVOICE PP 9014		5,000.00	14,050.00
41	09/02/2023	PURCHASE INVOICE PP 9015		6,900.00	7,150.00
42	10/02/2023	PURCHASE INVOICE PP 9016		2,000.00	5,150.00
43	11/02/2023	PURCHASE INVOICE PP 9017		3,000.00	2,150.00
44	12/02/2023	SALES INVOICE NN 913	5,000.00		7,150.00
45	13/02/2023	SALES INVOICE NN 914	2,500.00		9,650.00
46	14/02/2023	SALES INVOICE NN 915	6,000.00		15,650.00
47	15/02/2023	SALES INVOICE NN 916	2,000.00		17,650.00
48	16/02/2023	SALES INVOICE NN 917	1,200.00		18,850.00
49	17/02/2023	SALES INVOICE NN 918	3,000.00		21,850.00
50	18/02/2023	SALES INVOICE NN 919	4,000.00		25,850.00
51	19/02/2023	SALES INVOICE NN 920	500.00		26,350.00
52	20/02/2023	SALES INVOICE NN 921	6,000.00		32,350.00
53	21/02/2023	SALES INVOICE NN 922	7,000.00		39,350.00
54	22/02/2023	SALES INVOICE NN 923	8,000.00		47,350.00
55	23/02/2023	SALES INVOICE NN 924	9,000.00		56,350.00
56	24/02/2023	SALES INVOICE NN 925	3,000.00		59,350.00
57	25/02/2023	SALES INVOICE NN 926	490.00		59,840.00
58	26/02/2023	PURCHASE INVOICE PP 9018		7,000.00	52,840.00
59	27/02/2023	SALES INVOICE NN 927	560.00		53,400.00
60	28/02/2023	PAID VOUCHERVBG-803	450.00		53,850.00
61	01/03/2023	PAID VOUCHERVBG-804	600.00		54,450.00
62	02/03/2023	PAID VOUCHERVBG-805	700.00		55,150.00
63	03/03/2023	PAID VOUCHERVBG-806	800.00		55,950.00
64	04/03/2023	PAID VOUCHERVBG-807	900.00		56,850.00
65	05/03/2023	PURCHASE INVOICE PP 9019		6,000.00	50,850.00
66	06/03/2023	PURCHASE INVOICE PP 9020		700.00	50,150.00
67	07/03/2023	PURCHASE INVOICE PP 9021		800.00	49,350.00
68	08/03/2023	PURCHASE INVOICE PP 9022		2,300.00	47,050.00
69	09/03/2023	PURCHASE INVOICE PP 9023		1,200.00	45,850.00
70	10/03/2023	PURCHASE INVOICE PP 9024		2,000.00	43,850.00
71	11/03/2023	PURCHASE INVOICE PP 9025		1,234.00	42,616.00
72	12/03/2023	PURCHASE INVOICE PP 9026		5,600.00	37,016.00
73	13/03/2023	PURCHASE INVOICE PP 9027		500.00	36,516.00
74	14/03/2023	PURCHASE INVOICE PP 9028		2,400.00	34,116.00
75	15/03/2023	PURCHASE INVOICE PP 9029		1,300.00	32,816.00
76	16/03/2023	PURCHASE INVOICE PP 9030		7,000.00	25,816.00
77	17/03/2023	PURCHASE INVOICE PP 9031		500.00	25,316.00
78	18/03/2023	PURCHASE INVOICE PP 9032		600.00	24,716.00
79	19/03/2023	PURCHASE INVOICE PP 9033		600.00	24,116.00
80	20/03/2023	PURCHASE INVOICE PP 9034		700.00	23,416.00
81	21/03/2023	PURCHASE INVOICE PP 9035		800.00	22,616.00
82	22/03/2023	PURCHASE INVOICE PP 9036		1,000.00	21,616.00
83	23/03/2023	PURCHASE INVOICE PP 9037		500.00	21,116.00
84	24/03/2023	PURCHASE INVOICE PP 9038		890.00	20,226.00
85	25/03/2023	PURCHASE INVOICE PP 9039		4,500.00	15,726.00
86	26/03/2023	PURCHASE INVOICE PP 9040		1,200.00	14,526.00
87	27/03/2023	PURCHASE INVOICE PP 9041		1,000.00	13,526.00
88	28/03/2023	PURCHASE INVOICE PP 9042		500.00	13,026.00
89	29/03/2023	PURCHASE INVOICE PP 9043		3,400.00	9,626.00
90	30/03/2023	PURCHASE INVOICE PP 9044		600.00	9,026.00
91	31/03/2023	PURCHASE INVOICE PP 9045		700.00	8,326.00
92	01/04/2023	PURCHASE INVOICE PP 9046		230.00	8,096.00
93	02/04/2023	PURCHASE INVOICE PP 9047		90.00	8,006.00
94	03/04/2023	PURCHASE INVOICE PP 9048		500.00	7,506.00
95	04/04/2023	PURCHASE INVOICE PP 9049		700.00	6,806.00
96	05/04/2023	PURCHASE INVOICE PP 9050		300.00	6,506.00
97	06/04/2023	PURCHASE INVOICE PP 9051		690.00	5,816.00
98	07/04/2023	PURCHASE INVOICE PP 9052		1,200.00	4,616.00
99	08/04/2023	PURCHASE INVOICE PP 9053		500.00	4,116.00
100	09/04/2023	PURCHASE INVOICE PP 9054		1,200.00	2,916.00
101	TOTAL		164,900.00	161,984.00	2,916.00
ALAA

Cell Formulas
Range	Formula
E2,E101	E2	=C2-D2
E3:E100	E3	=E2+C3-D3
C101:D101	C101	=SUM(C2:C100)

ORIGINAL.xlsm
	A	B	C	D	E
1	DATE	DESCRIPTION	DEBIT	CREDIT	BALANCE
2	01/01/2023	SALES INVOICE NN 927	7,000.00		7,000.00
3	01/01/2023	SALES INVOICE NN 928	6,000.00		13,000.00
4	03/01/2023	SALES INVOICE NN 929	7,900.00		20,900.00
5	04/01/2023	SALES INVOICE NN 930	8,000.00		28,900.00
6	05/01/2023	SALES INVOICE NN 931	40,000.00		68,900.00
7	06/01/2023	CASH VOUCHER NO VM6007		4,000.00	64,900.00
8	07/01/2023	PURCHASE INVOICE PP 9055		6,000.00	58,900.00
9	08/01/2023	PURCHASE INVOICE PP 9056		7,000.00	51,900.00
10	09/01/2023	PURCHASE INVOICE PP 9057		7,000.00	44,900.00
11	10/01/2023	PURCHASE INVOICE PP 9058		8,000.00	36,900.00
12	11/01/2023	PURCHASE INVOICE PP 9059		9,000.00	27,900.00
13	12/01/2023	SALES INVOICE NN 932	4,500.00		32,400.00
14	12/01/2023	SALES INVOICE NN 933	7,900.00		40,300.00
15	12/01/2023	CASH VOUCHER NO VM6008		6,000.00	34,300.00
16	12/01/2023	CASH VOUCHER NO VM6009		5,000.00	29,300.00
17	12/01/2023	CASH VOUCHER NO VM6010		1,200.00	28,100.00
18	12/01/2023	CASH VOUCHER NO VM6011		4,000.00	24,100.00
19	12/01/2023	PAID VOUCHERVBG-808	6,000.00		30,100.00
20	19/01/2023	PAID VOUCHERVBG-809	5,000.00		35,100.00
21	20/01/2023	PAID VOUCHERVBG-810	8,000.00		43,100.00
22	21/01/2023	PAID VOUCHERVBG-811	2,000.00		45,100.00
23	22/01/2023	SALES INVOICE NN 934	5,000.00		50,100.00
24	23/01/2023	SALES INVOICE NN 935	1,200.00		51,300.00
25	24/01/2023	SALES INVOICE NN 936	400.00		51,700.00
26	25/01/2023	SALES INVOICE NN 937	500.00		52,200.00
27	26/01/2023	SALES INVOICE NN 938	600.00		52,800.00
28	27/01/2023	SALES INVOICE NN 939	700.00		53,500.00
29	28/01/2023	SALES INVOICE NN 940	1,500.00		55,000.00
30	29/01/2023	SALES INVOICE NN 941	2,300.00		57,300.00
31	30/01/2023	SALES INVOICE NN 942	4,500.00		61,800.00
32	31/01/2023	SALES INVOICE NN 943	4,500.00		66,300.00
33	01/02/2023	SALES INVOICE NN 944	230.00		66,530.00
34	02/02/2023	SALES INVOICE NN 945	1,300.00		67,830.00
35	03/02/2023	SALES INVOICE NN 946	200.00		68,030.00
36	04/02/2023	SALES INVOICE NN 947	500.00		68,530.00
37	05/02/2023	SALES INVOICE NN 948	1,300.00		69,830.00
38	06/02/2023	SALES INVOICE NN 949	14,750.00		84,580.00
39	07/02/2023	CASH VOUCHER NO VM6012		7,000.00	77,580.00
40	08/02/2023	CASH VOUCHER NO VM6013		1,000.00	76,580.00
41	09/02/2023	CASH VOUCHER NO VM6014		1,500.00	75,080.00
42	10/02/2023	CASH VOUCHER NO VM6015		3,000.00	72,080.00
43	11/02/2023	CASH VOUCHER NO VM6016		4,000.00	68,080.00
44	12/02/2023	CASH VOUCHER NO VM6017		5,000.00	63,080.00
45	13/02/2023	CASH VOUCHER NO VM6018		2,200.00	60,880.00
46	14/02/2023	CASH VOUCHER NO VM6019		1,300.00	59,580.00
47	15/02/2023	CASH VOUCHER NO VM6020		500.00	59,080.00
48	16/02/2023	CASH VOUCHER NO VM6021		3,500.00	55,580.00
49	17/02/2023	SALES INVOICE NN 950	5,600.00		61,180.00
50	18/02/2023	CASH VOUCHER NO VM6022		7,000.00	54,180.00
51	19/02/2023	CASH VOUCHER NO VM6023		5,000.00	49,180.00
52	20/02/2023	CASH VOUCHER NO VM6024		2,200.00	46,980.00
53	21/02/2023	CASH VOUCHER NO VM6025		1,300.00	45,680.00
54	22/02/2023	PAID VOUCHERVBG-812	7,000.00		52,680.00
55	23/02/2023	PAID VOUCHERVBG-813	600.00		53,280.00
56	24/02/2023	PAID VOUCHERVBG-814	500.00		53,780.00
57	TOTAL		155,480.00	101,700.00	53,780.00
ALIAA

Cell Formulas
Range	Formula
E2,E57	E2	=C2-D2
E3:E56	E3	=E2+C3-D3
C57:D57	C57	=SUM(C2:C56)

ORIGINAL.xlsm
	A	B	C	D	E
1	DATE	DESCRIPTION	DEBIT	CREDIT	BALANCE
2	01/01/2023	SALES INVOICE NN 951	4,000.00		4,000.00
3	02/01/2023	SALES INVOICE NN 952	9,000.00		13,000.00
4	02/01/2023	SALES INVOICE NN 953	8,000.00		21,000.00
5	02/01/2023	SALES INVOICE NN 954	8,000.00		29,000.00
6	02/01/2023	SALES INVOICE NN 955	45,000.00		74,000.00
7	02/01/2023	SALES INVOICE NN 956	1,200.00		75,200.00
8	02/01/2023	SALES INVOICE NN 957	3,000.00		78,200.00
9	02/01/2023	SALES INVOICE NN 958	2,000.00		80,200.00
10	02/01/2023	SALES INVOICE NN 959	1,500.00		81,700.00
11	10/01/2023	SALES INVOICE NN 960	1,300.00		83,000.00
12	11/01/2023	SALES INVOICE NN 961	1,400.00		84,400.00
13	12/01/2023	SALES INVOICE NN 962	5,000.00		89,400.00
14	12/01/2023	SALES INVOICE NN 933	3,000.00		92,400.00
15	12/01/2023	CASH VOUCHER NO VM6025		6,000.00	86,400.00
16	12/01/2023	SALES INVOICE NN 934	7,000.00		93,400.00
17	12/01/2023	CASH VOUCHER NO VM6026		9,000.00	84,400.00
18	12/01/2023	CASH VOUCHER NO VM6027		10,000.00	74,400.00
19	12/01/2023	CASH VOUCHER NO VM6028		12,000.00	62,400.00
20	12/01/2023	CASH VOUCHER NO VM6029		14,000.00	48,400.00
21	12/01/2023	PAID VOUCHERVBG-815	4,000.00		52,400.00
22	12/01/2023	PAID VOUCHERVBG-816	5,000.00		57,400.00
23	22/01/2023	PAID VOUCHERVBG-817	12,000.00		69,400.00
24	23/01/2023	PAID VOUCHERVBG-818	1,200.00		70,600.00
25	24/01/2023	PAID VOUCHERVBG-819	120.00		70,720.00
26	25/01/2023	PAID VOUCHERVBG-820	450.00		71,170.00
27	26/01/2023	SALES INVOICE NN 938	600.00		71,770.00
28	27/01/2023	SALES INVOICE NN 939	1,200.00		72,970.00
29	28/01/2023	SALES INVOICE NN 940	1,300.00		74,270.00
30	29/01/2023	SALES INVOICE NN 941	3,000.00		77,270.00
31	30/01/2023	SALES INVOICE NN 942	4,000.00		81,270.00
32	31/01/2023	SALES INVOICE NN 943	2,000.00		83,270.00
33	01/02/2023	SALES INVOICE NN 944	2,200.00		85,470.00
34	02/02/2023	SALES INVOICE NN 945	120.00		85,590.00
35	03/02/2023	SALES INVOICE NN 946	1,500.00		87,090.00
36	04/02/2023	SALES INVOICE NN 947	300.00		87,390.00
37	05/02/2023	SALES INVOICE NN 948	1,200.00		88,590.00
38	06/02/2023	SALES INVOICE NN 949	1,000.00		89,590.00
39	07/02/2023	CASH VOUCHER NO VM6012		7,800.00	81,790.00
40	08/02/2023	CASH VOUCHER NO VM6013		1,300.00	80,490.00
41	09/02/2023	CASH VOUCHER NO VM6014		1,200.00	79,290.00
42	10/02/2023	CASH VOUCHER NO VM6015		1,000.00	78,290.00
43	11/02/2023	CASH VOUCHER NO VM6016		1,700.00	76,590.00
44	12/02/2023	CASH VOUCHER NO VM6017		1,800.00	74,790.00
45	13/02/2023	CASH VOUCHER NO VM6018		8,000.00	66,790.00
46	14/02/2023	CASH VOUCHER NO VM6019		9,000.00	57,790.00
47	15/02/2023	CASH VOUCHER NO VM6020		8,700.00	49,090.00
48	16/02/2023	CASH VOUCHER NO VM6021		1,200.00	47,890.00
49	17/02/2023	SALES INVOICE NN 950	4,000.00		51,890.00
50	18/02/2023	CASH VOUCHER NO VM6022		100.00	51,790.00
51	19/02/2023	CASH VOUCHER NO VM6023		200.00	51,590.00
52	20/02/2023	CASH VOUCHER NO VM6024		300.00	51,290.00
53	21/02/2023	CASH VOUCHER NO VM6025		400.00	50,890.00
54	22/02/2023	PAID VOUCHERVBG-812	1,200.00		52,090.00
55	23/02/2023	PAID VOUCHERVBG-813	500.00		52,590.00
56	24/02/2023	PAID VOUCHERVBG-814	550.00		53,140.00
57	25/02/2023	PAID VOUCHERVBG-815	15,000.00		68,140.00
58	26/02/2023	CASH VOUCHER NO VM6026		40,000.00	28,140.00
59	27/02/2023	CASH VOUCHER NO VM6027		15,000.00	13,140.00
60	TOTAL		161,840.00	93,700.00	68,140.00
ALOAA

Cell Formulas
Range	Formula
E2,E60	E2	=C2-D2
E3:E59	E3	=E2+C3-D3
C60	C60	=SUM(C2:C59)
D60	D60	=SUM(D2:D56)

first case when textbox1,textbox2 are empty

second case when there are two dates

at leas data for each sheet could be 4000 rows and could reach 12 sheets
any suggestion to make the things easy I'm in all ears.

 

[DanteAmor]

Hi @Alaa mg

Do the following:
1. Add a commandbutton to your form.
2. Run the form.
3. Capture the 2 dates or delete the 2 dates.
4. Press the button.
5. Adjust on this line the concepts that must be added.

arr = Array("NAME", "SALES INVOICE", "PURCHASE INVOICE", "CASH VOUCHER", "PAID VOUCHER")​

6. The code only considers the sheets that have the text "DATE" in cell A1.

Considerations:
- The code validates that you capture both dates or none.
- At the beginning of the form the process may be slow, because it is loading all the information in arrays.
- But once the information is loaded, the filters will be faster.

Put all of the following code in your form:

Option Explicit
  Dim b As Variant, c As Variant
  Dim arr As Variant
  Dim di1 As Object, di2 As Object

Private Sub CommandButton1_Click()
  Dim tb1 As String, tb2 As String
  Dim i As Long, j As Long, m As Long, nRow As Long, nCol As Long
  Dim d As Variant
  
  tb1 = TextBox1.Value
  tb2 = TextBox2.Value
  If tb1 = "" And tb2 <> "" Then
     MsgBox "Missing date"
     TextBox1.SetFocus
     Exit Sub
  End If
  If tb1 <> "" And tb2 = "" Then
     MsgBox "Missing date"
     TextBox2.SetFocus
     Exit Sub
  End If
  If tb1 <> "" Then
    If Not IsDate(tb1) Then
      MsgBox "Enter valid date"
      TextBox1.SetFocus
      Exit Sub
    End If
  End If
  If tb2 <> "" Then
    If Not IsDate(tb2) Then
      MsgBox "Enter valid date"
      TextBox2.SetFocus
      Exit Sub
    End If
  End If
  
  If tb1 = "" And tb2 = "" Then
    ListBox1.List = c
  Else
    ReDim d(1 To UBound(c, 1), 1 To UBound(c, 2))
    For i = 1 To UBound(c, 1)
      d(i, 1) = c(i, 1)
    Next
    For m = 2 To UBound(c, 2)
      d(1, m) = c(1, m)
    Next
    
    For i = 1 To UBound(b, 1)
      If b(i, 1) >= CDate(tb1) And b(i, 1) <= CDate(tb2) Then
        nRow = di1(b(i, 2))
        nCol = di2(b(i, 6))
        d(nRow, nCol) = d(nRow, nCol) + b(i, 3) + b(i, 4)
      End If
    Next
    
    For i = 2 To UBound(c, 1)
      For j = 2 To UBound(c, 2)
        If d(i, j) = "" Then d(i, j) = 0
        d(i, j) = Format(d(i, j), "#,##0.00")
      Next
    Next
    ListBox1.List = d
  End If
End Sub

Private Sub UserForm_Activate()
  Dim sh As Worksheet
  Dim a() As Variant
  Dim i As Long, j As Long, k As Long, lr As Long, m As Long, n As Long
  Dim nRow As Long, nCol As Long
  
  arr = Array("NAME", "SALES INVOICE", "PURCHASE INVOICE", "CASH VOUCHER", "PAID VOUCHER")
  Set di1 = CreateObject("Scripting.Dictionary")
  Set di2 = CreateObject("Scripting.Dictionary")
  
  For Each sh In Sheets
    If sh.Range("A1").Value = "DATE" Then
      n = n + 1
      di2(sh.Name) = n + 1
      lr = lr + sh.Range("B" & Rows.Count).End(3).Row - 1
    End If
  Next
  ReDim b(1 To lr, 1 To 6)                      'data for all sheets
  ReDim c(1 To UBound(arr, 1) + 1, 1 To n + 1)  'sum of all dates
  For m = 0 To UBound(arr)
    c(m + 1, 1) = arr(m)
    di1(arr(m)) = m + 1
  Next
  
  n = 1
  For Each sh In Sheets
    Erase a
    If sh.Range("A1").Value = "DATE" Then
      n = n + 1
      c(1, n) = sh.Name
      a = sh.Range("A2:E" & sh.Range("B" & Rows.Count).End(3).Row).Value2
      For i = 1 To UBound(a, 1)
        k = k + 1
        For j = 1 To UBound(a, 2)
          If j = 2 Then
            For m = 1 To UBound(arr)
              If Left(a(i, 2), Len(arr(m))) = arr(m) Then
                b(k, j) = arr(m)
                Exit For
              End If
            Next
          Else
            b(k, j) = a(i, j)
          End If
        Next
        b(k, UBound(b, 2)) = sh.Name
      Next
    End If
  Next
  
  For i = 1 To UBound(b, 1)
    nRow = di1(b(i, 2))
    nCol = di2(b(i, 6))
    c(nRow, nCol) = c(nRow, nCol) + b(i, 3) + b(i, 4)
  Next
  
  For i = 2 To UBound(c, 1)
    For j = 2 To UBound(c, 2)
      If c(i, j) = "" Then c(i, j) = 0
      c(i, j) = Format(c(i, j), "#,##0.00")
    Next
  Next
  
  With ListBox1
    .ColumnCount = n
    .List = c
  End With
End Sub

Comment on your test results with all the sheets and all the data.
Cordially
Dante Amor

 

[Alaa mg]

6. The code only considers the sheets that have the text "DATE" in cell A1.

sorry about first picture doesn't copy header in row 1 when I posted the thread !
I'm really grateful for your assistance .
thank you so much