exclude positive values for lastrow across sheets

Hi again Alaa

In the attached revised file, your button is now labelled "non-positive values" (since in theory you could have zero balances) and I have altered your code, as shown in bold below.

I didn't use Sheets(i) and the If test now checks if the balance is non-positive (before adding the the array and incremementing counter k). Note that the array doesn't need to be 1,000 rows - see changes to the Redim statement)

After the array is populated, I've adjusted the code for the BALANCES sheet to create the table formatting needed for the non-positive values/ TOTAL (using k) and adjusted the column width of E (since with many sheets you might have bigger values. (I didn't bother to adjust your .Resize - that doesn't need UBound of 1000 but k could be used).

I've added some comments to help:

Sub test1()
    Dim lrow%, k%, ttl&, tt2&, tt3&
    Dim a()
    Dim b()
    Dim ws As Worksheet

 ' size array to number of sheets   
ReDim b(1 To ThisWorkbook.Worksheets.Count, 1 To 6)

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

    'For i = 1 To Worksheets.Count
    '      With Sheets(i)

    For Each ws In ThisWorkbook.Worksheets
        With ws
            lrow = .Cells(Rows.Count, "e").End(xlUp).Row 'Find the last row of the sheets value
            ' see if account balance is non-positive
            If .Name <> "BALANCES" And .Cells(lrow, 5) < 0 Then
                'if not balances sheet
                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) = .Name
                b(k, 4) = a(1, 3)
                 b(k, 5) = a(1, 4)
                b(k, 6) = a(1, 5) '
                 'recalculate balance Value
                 ttl = a(1, 3) + ttl
                 tt2 = a(1, 4) + tt2
                tt3 = a(1, 5) + tt3
             End If
          End With
    Next ws

    With Sheets("BALANCES")
        ' clear and format table ahead of new values
        '.[a2:F10000].ClearContents
        .[a2:F4].ClearContents
        .[a4:F10000].Delete
        .Range("a3:F" & k + 1).EntireRow.Insert CopyOrigin:=xlFormatFromLeftOrAbove

        ' show new values
        .[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
        ' adjust Balances column width
        .Columns(6).AutoFit
    End With

End Sub

Hope this is what you want- it should work with any number of sheets.