I want to use VBA to inert a formula into a cell.

I want to get the contents of Cell B7 on the "Balance Sheet" and insert that
into cell B$ on the current sheet.
Here is what I have but it's not working:
Range("B4").Select
ActiveCell.FormulaR1C1 = "='Balance Sheet'!B7"

When I do that I get a #NAME? error.

Help please!

And... thanks!

Jon