وعليكم السلام
جرب الكود التالي عله يفي بالغرض
Sub Test()
Dim ws As Worksheet
Dim lr As Long
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets(Array("1", "2", "3", "4", "5"))
lr = LastRowNonZero(ws.Name, "J") - 1
ws.Range("L2").Value = Application.WorksheetFunction.Sum(ws.Range("J4:J" & lr))
Next ws
Application.ScreenUpdating = True
End Sub
Public Function LastRowNonZero(ShtName As String, ColumnLetter As String) As Long
Dim cel As Range
Set cel = Sheets(ShtName).Cells(Rows.Count, ColumnLetter).End(xlUp)
Do While cel.Value <= 0 Or Not IsNumeric(cel)
Set cel = cel.Offset(-1)
Loop
LastRowNonZero = cel.Row
End Function