I think I've worked out why Its not working.

It calls ActiveSheet, which is referring to the "CopyPasteFormula.xlsm" sheet, not the one where you are pressing the copy button... I need to somehow work out how to find out sheet that was active before

Still don't know why it opens it as an addon every time though :|