Return sum value of filtered/visible list

Question

I have two workbooks: "TEST" and "Report". I have used the following code:

MsgBox Sum_Visible_Cells(Worksheets("Raw Data").Range("M2:M" & LastRow(Worksheets("Raw Data"))))

and it returns the sum value in a messagebox. I have also been able to return the sum in a cell on the worksheet "Raw Data" in the workbook "TEST".

How can I make excel return the value in worksheet "Sheet1" , range "L9" on the workbook "Report"?

Answer 1

You don't need vba to do this. In cell Sheet1!L9 enter the formula

=SUBTOTAL(109,'Raw Data'!$M:$M)

To do it in VBA use

Application.Workbooks("Report.xlsm").Worksheets("Sheet1").Range("L9") = "Result of your function"

Note: you must include file extension, and the workbook must be open.

EDIT:
Sum_Visible_Cells is not a worksheet function.

try this (assuming the code is in workbook Test)

Application.Workbooks("Report.xlsm").Worksheets("Sheet1").Range("L9") = _
    Application.WorksheetFunction.Subtotal(109, ThisWorkbook.Worksheets("Raw Data").Columns(13))

From your comment Range("M2:M) I guess you want to exclude row 1 from the total.
So long as M1 is not numeric summing the whole column will be fine. If row 1 is numeric and you want to exclude it try

Dim rng as Range
With ThisWorkbook.Worksheets("Raw Data")
    Set rng = .Range(.Cells(2,13), .Cells(.Rows.Count, 13).End(xlUp))
End With 
Application.Workbooks("Report.xlsm").Worksheets("Sheet1").Range("L9") = _
    Application.WorksheetFunction.Subtotal(109, rng)

Answer 2

Does it have to be done in the order you describe?

If you use the suggestion in the attached link "VBA to get value from closed file" to add the GetValue function (make public) to retrieve the information from the Test Workbook you would have:

In TEST Workbook:

• Create a range containing your existing formula e.g. Sheet1, Cell A1 Sum_Visible_Cells(Worksheets("Raw Data").Range("M2:M" & LastRow(Worksheets("Raw Data")))) formula

In REPORT Workbook use the GetValue() function in Sheet1 Cell L9 :

=GetValue(p, f, s, a)

where

• p = "Path to TEST Workbook"
• f = "TEST.xls"
• s = "Sheet1"
• a = "A1"