Using VBA to have a cell equal a sum of cells in another worksheet

[MasonR2]

Hello, I'm trying to create a vba macro that allows me to clear the current value of cells in one spreadsheet and then have the cleared cell be set to the sum of a range of cells in another sheet. I'm trying to have the sum start from the next unpopulated row and go down the column until it reaches a different row. This is what I currently have:

    ''Set Accounts Recievable to 0 while keeping sum of new sales
    Dim rec As Range
    rec = Worksheets("Invoices").Range(Worksheets("Invoices").Range("E2").End(xlDown).Offset(1, 0), Worksheets("Invoices").Range("E2").End(xlDown).Offset(10, 1))
    Worksheets("BS & IS").Range("C6") = WorksheetFunction.Sum(rec)
    'Set Accounts Payable to 0 while keeping sum of new purchases
    Dim pay As Range
    pay = Worksheets("Purchase Orders").Range(Worksheets("Purchase Orders").Range("F2").End(xlDown).Offset(1, 0), Worksheets("Purchase Orders").Range("F2").End(xlDown).Offset(10, 1))
    Worksheets("BS & IS").Range("F5") = WorksheetFunction.Sum(pay)

Essentially I want to set the cells C6 and F5 in the "BS & IS" sheet to be set to the sum of cells in the "Invoices" and "Purchase Orders" that start from the next unpopulated row in a column and goes down (arbitrarily)10 rows.

 I'm new to visual basic so I don't know all the syntax for functions just yet, and before I made the code above I was trying to imitate the =SUM("cell:cell") format in the spreadsheets:

WorksheetFunction.Sum("Worksheets(""Invoices"").Range(""E2"").End(xlDown).Offset(1, 0): Worksheets(""Invoices"").Range(""E2"").End(xlDown).Offset(10, 1)")

I've been getting compile errors using either of them. Any help would be greatly appreciated!

[#2]

@MasonR2 

rec and pay are Range objects. You have to use the keyword Set to assign them:

    ''Set Accounts Receivable to 0 while keeping sum of new sales
    Dim rec As Range
    With Worksheets("Invoice")
        Set rec = .Range(.Range("E2").End(xlDown).Offset(1, 0), .Range("E2").End(xlDown).Offset(10, 1))
    End With
    Worksheets("BS & IS").Range("C6") = WorksheetFunction.Sum(rec)
    'Set Accounts Payable to 0 while keeping sum of new purchases
    Dim pay As Range
    With Worksheets("Purchase Orders")
        Set pay = .Range(.Range("F2").End(xlDown).Offset(1, 0), .Range("F2").End(xlDown).Offset(10, 1))
    End With
    Worksheets("BS & IS").Range("F5") = WorksheetFunction.Sum(pay)

WorksheetFunction.Sum expects numbers and/or ranges as argument, not a text string, so WorksheetFunction.Sum("...") won't do what you want.

[#3]

@Hans Vogelaar 

Thank you! The code is working and setting the value of the cells to 0 as it should. However, is it possible to set the cells formula as =SUM(" : ") for the range in the Invoices and Purchase Orders sheets such that the cells in BS & IS update with new entries? Would I have to do 

Worksheets("BS & IS").Range("C6").Formula = ...

 in order for this to happen?

[#4]

@MasonR2 

Yes. In that case, don't use WorksheetFormula, but set the formula of those two cells.