I'm trying to reference a alue that is stored in a cell.

Cell "S1" has the value "J2" stored in it.

in VBA I'm using this code:

cell = Sheets("Form").Range("S1")
Sheets("Archive").Range(cell).Paste

but it isn't working. How do you do this?

What are you attempting? Do you want to copy the value from cell Form.S1 to cell Archive.S1?

Sheets("Archive").Range("S1").Formula = Sheets("Form").Range("S1").Formula

cell "S1" is essentially a *pointer. In this example is contains the value "J2", which is where I want to paste my data.

the value in "S1" is determining where the number in "E15" is going.("J2")

So, do you want it to grab the "pointer," make a new "pointer," or the final value?

"S1" is pointing to the cell where the final value is placed.

"E15" has the value 1.
"S1" has the value "J2".
I want "J2" to have the value of 1.

But the value of "S1" changes and VBA won't let me use the code in my first post.

Of course it doesn't let you use your code; the flow of logic is incompatible with VBA. I'm surprised it actually compiled. After all, after I read it, I had to ask you what you wanted.

This line (It's really one line; I blame the line wrapping, myself.) will copy whatever is displayed in the cell "S1" (and not the pointer itself):
Sheets("Archive").Range("J2").Formula = Sheets("Form").Range("S1").Value

If you wanted the pointer, I'd just drop the VBA attempt and change J2's formula to read:
=Form!$S$1

But it won't always point to "J2". That's the problem.