Macro for saving cell data to another sheet

Very simple.

Range("A2").value = Range("A1").value

Where A1 holds the formula, A2 will become the result. You can also copy the formula then paste special then select values. to do this in macro you do:

    range("A1:A5").Copy
    Range("B1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False