Using VBA to enter a SumProduct formula into a cell

I need to use VBA to build a SumProduct formula and enter it into various cells in my worksheet. This is what I have done so far:

Range("E6").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(('Data This Week'!$B$5:$B$992=B6)*('Data This Week'!$D$5:$D$992=D6)*('Data This Week'!$L$5:$L$992))-(SUMPRODUCT(('Data Last Week'!$B$5:$B$992=B6)*('Data Last Week'!$D$5:$D$992=D6)*('Data Last Week'!$O$5:$O$992)))"

When I try to run the procedure, I get Run-time error 1004 "Application-defined or object-defined error". The line starting with "ActiveCell" gets highlighted by the debugger.

The formula works fine when typed directly into the cell. So I have tried to use the macro recorder to record myself entering the formula, but then I get a message saying "Cannot be recorded."

Why is this error occuring and what would I need to do to fix it?