sum column in non blank sheets of 52 total

i have a workbook with 52 sheets i can find the none blank worsheets.
i need to total several column and put on a summary sheet.
here is my code so far. tried many ways but getting error or wrong results at this line.

Sld = WorksheetFunction.Sum(ws.Cells(8), (ws.Cells(8)))



Sub Test_Totals_Sheets()
Dim r As String
Dim wbk As Workbook
Dim ws As Worksheet
Dim lastRow1 As Long
Dim lastsheet As Long

Dim Sold As Double 'Sold coumn
Dim Subtotal As Double 'Subtotal column


Dim Sld As Long ' SOLD
Dim subT As Long 'Subtotal

Dim Rng As Range

Dim i As Long
Dim Ntext As String

r = 1
Dim Sheet As String
Set wbk = Workbooks("2023-Weeks-Test.xlsm")
' Find number of worksheets in workbook
lastsheet = wbk.Sheets.Count

Set ws = wbk.Worksheets("Week-" + r)

'find last row
lastRow1 = ws.Cells(Rows.Count, 1).End(xlUp).Row


For i = 1 To 52

Set wbk = Workbooks("2023-Weeks-Test.xlsm")
Set ws = wbk.Worksheets("Week-" + r)

If ws.Cells(1, 1) <> "" Then
Worksheets("Week-" + r).Select
Sld = 0
Sold = 0

lastRow1 = ws.Cells(Rows.Count, 1).End(xlUp).Row

Sld = WorksheetFunction.Sum(ws.Cells(8), (ws.Cells(8)))

MsgBox ("Sold Total =" & Sold)
'do it here sheet is not blank
lastRow1 = ws.Cells(Rows.Count, 1).End(xlUp).Row
Ntext = lastRow1
MsgBox ("NOT Blank Week-" + r)
MsgBox ("last row is " + Ntext)
Else

'do it here if sheet is blank
MsgBox ("is Blank week " + r)

End If

r = r + 1

Next i




End Sub

Ws.cells(8) refers to only the row number of a cell. If you are trying to select a cell, say A8, you’ll need to use ws.cells(8,1). Also, if you are trying to get the sum of a range, say sum(Range(“A8:E8”)), that should be written as sum(ws.Range(ws.cells(8,1),ws.cells(8,5))). I hope this helps.

Hi maniacb !

As Ws.Cells(8) is cell H1 of Ws … And yes its original codeline try to sum only H1 which has no sense …