since your formula is written in A1 style referencing, why would you use
formulaR1C1 telling excel it is in R1C1 style?

ActiveCell.Formula = "='Balance Sheet'!B7"

should work.

--
Regards,
Tom Ogilvy


"jonco" wrote:

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