Over flow when try get last TOTAL row across sheets

[Alaa mg]

hello
first this original code from this thread
create list by extract amounts for the last row for each sheet name
but I need modifying by get the last total row for each sheet , my problem after getting the last TOTAL for each sheet in BALANCES sheet should also insert TOTAL row to sum for all of sheets, but it shows overflow error as I comment out in theses the lines

'ttl = a(1, 3) + ttl
 'tt2 = a(1, 4) + tt2
  'tt3 = a(1, 5) + tt3

Option Explicit
Option Compare Text
Sub test1()
Dim i%, lrow%, k%, ttl%, tt2%, tt3%
Dim a()
Dim b()
ReDim b(1 To 10000, 1 To 6)

Sheets("BALANCES").[a2:F10000].Clear


For i = 1 To Worksheets.Count
      With Sheets(i)
            If Sheets(i).Name <> "BALANCES" Then 'Loop except balances sheet
             lrow = .Cells(Rows.Count, "e").End(xlUp).Row 'Find the last row of the sheets value
             a = .Range(.Cells(lrow, "A"), .Cells(lrow, "e")).Value 'Store last row into array
             k = k + 1
             b(k, 1) = k
             b(k, 2) = "OPENING BALANCE " & Date 'Date value
             b(k, 3) = Sheets(i).Name
             b(k, 4) = a(1, 3)
              b(k, 5) = a(1, 4)
             b(k, 6) = a(1, 5) '
              'Balance Value
             ' ttl = a(1, 3) + ttl
             ' tt2 = a(1, 4) + tt2
             'tt3 = a(1, 5) + tt3
             End If
      End With
   
Next i
With Sheets("BALANCES")
    .[a2].Resize(UBound(b, 1), UBound(b, 2)).Value = b 'Call out Array
    lrow = .Cells(Rows.Count, "a").End(xlUp).Row + 1
    .Cells(lrow, "A").Value = "TOTAL"
    .Cells(lrow, "d").Value = ttl
    .Cells(lrow, "E").Value = tt2
    .Cells(lrow, "F").Value = tt3
End With
 
End Sub

ALL.xlsm
	A	B	C	D	E
1	DATE	Describe	DEBIT	CREDIT	BALANCE
2	05/01/2022	NOT PAID	1233		1233
3	06/01/2022	NOT PAID	1233		2466
4	07/01/2022	PAID		2000	466
5	08/01/2022	PAID		400	66
6	TOTAL		2,466.00	2400	66.00
ALA

Cell Formulas
Range	Formula
C6:D6	C6	=SUM(C2:C5)
E6	E6	=C6-D6

ALL.xlsm
	A	B	C	D	E
1	DATE	Describe	DEBIT	CREDIT	BALANCE
2	08/01/2022				0
3	09/01/2022	NOT PAID	2000		2000
4	11/01/2022	NOT PAID	2000	1000	3000
5	11/01/2022	NOT PAID	2000	4000	1000
6	TOTAL		6000	5000	1000
MN

Cell Formulas
Range	Formula
C6:D6	C6	=SUM(C3:C5)

ALL.xlsm
	A	B	C	D	E	F
1	ITEM	DETAILES	NAMES	DEBIT	CREDIT	BALANCE
2
3
4
BALANCES

result

ALL.xlsm
	A	B	C	D	E	F
1	ITEM	DETAILES	NAMES	DEBIT	CREDIT	BALANCE
2		OPENING BALANCE 11/07/2023	ALA	2466	2,400.00	66.00
3		OPENING BALANCE 11/07/2023	MN	6000	5,000.00	1,000.00
4	TOTAL			8466	7,400.00	1,066.00
BALANCES

 

[Kevin_Li]

Dim i%, lrow%, k%, ttl%, tt2%, tt3%

 

[Kevin_Li]

Try changing this line to : Dim i%, lrow%, k%, ttl&, tt2&, tt3&

 

[Alaa mg]

great !
it works without any error.
thank you so much .

 

[Kevin_Li]

I'm glad to be able to help you