Get Last Row Value to a Row from closed workbook

[arijitirf]

Hi!!!

I need to break my original excel file into 6 different workbooks due to it's size. Now, I want to put the total values of last row for each sheet of that 6 workbooks to 6 different cell in a new excel workbook without opening it. Actually I want to create a macro so that it show me the total value of the last row of each sheet of that 6 workbooks whenever I open the new Workbook namely "Control.xlsx"


Public Sub SumLastRows()

Dim total As Long
Dim ws As Worksheet
Dim lastRow As Long

total = 0
For Each ws In ActiveWorkbook.Worksheets
lastRow = ws.Cells(ws.Rows.Count, "J").End(xlUp).Row
If IsNumeric(ws.Cells(lastRow, "J").Value) Then
total = total + ws.Cells(lastRow, "J").Value
Else
Debug.Print "Cell J" & CStr(lastRow) & " on sheet '" & ws.Name & "' is not numeric"
End If
Next

MsgBox "Total is : " & CStr(total), vbOKOnly + vbInformation, "Sum Last Rows"

End Sub
above code was written by wideboydixon, which I want to use for that 6 workbooks and get result into "Control.xlsx"

Help Required.