Macro to sum values from another Workbook with Multiple Sheets

Question

I am working on a project that requires me to sum multiple values in another excel workbook.

The other Workbook contains, say, 120 sheets. I want to take a certain value from each sheet, and sum it along with the rest. The cell reference for all the 120 values is fixed, for example J3.

Another thing worth mentioning (maybe) is that the sheet code names aren't organized (i.e. Sheet 1 all the way to Sheet 120), there have been several sheets added/removed in between.

Right now I'm getting

Run Time Error 424 Object Required

Sub SumProject1P()
    Dim Project1P As Workbook
    Dim reserves
    Dim WS_Count As Integer
    Dim i As Integer
    Dim V As Variant

    Set Project1P = Workbooks.Open("FILE PATH")
    WS_Count = Workbook.Worksheets.Count
    V = Workbook.Worksheets
    reserves = sumrange(Workbook.Worksheets(V).range(Cells(1, 8)))
End Sub


Function sumrange(range)
    summ = 0
    For i = 1 To WS_Count
        summ = summ + reserves
    Next
    reserves = summ
End Function

I hope to receive feedback and input.

Answer 1

Here you go

Sub SumProject1P()

Dim Project1P As Workbook
Dim reserves As Long
Dim WS_Count As Integer
Dim filePath As String

    filePath = "Enter you file path"

    Set Project1P = Workbooks.Open(filePath, ReadOnly:=True)
    WS_Count = Project1P.Worksheets.Count


   For i = 1 To WS_Count
    If Sheets(i).range("J3") <> "" And IsNumeric(Sheets(i).range("J3")) Then
        reserves = reserves + Sheets(i).range("J3")
    End If
   Next

   MsgBox "Total of all sheets :" & reserves
End Sub