Error when pasting formula

[CLoos]

I always get this wrong! I don't know why. I am getting a runtime error 1004 for PasteSpecial method of Range class failed on the .pastespecial code line, why?
and of course, how do I fix it?

ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-3]"
ActiveCell.Copy
  Range("P5").End(xlDown).Select
  ActiveCell = ActiveCell.Offset(0, 1).Select
  Range(Selection, Selection.End(xlUp)).Select
  Selection.PasteSpecial Paste:=xlPasteFormulas
  Application.CutCopyMode = False

 

[Hui]

CLoos

You code as is, works

I would simplify it slightly to:

ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-3]"
ActiveCell.Copy
Range("P5").End(xlDown).Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False

I suspect the issue is that the code is in a Code Module and not in a Worksheet Module.

Try moving it top a Worksheet Module and see if it runs?

Because the Worksheet Module knows about the Range as it belongs to the Worksheet, A Code Module you need to tell it where the range belongs, ie the Worksheet