Thank you for both of your suggestions, but for some reason neither seem to
work. Allow me to clarify a bit more, as I have a sneaking suspicion that the
formula in A2 may have an effect on the macro if they are "firing" at
different times.
Cell A2 has the formula =if(E2="Y",randbetween(100,999),"")

The intention is for the Y in E2, which is created by another formula
connected to several other cells, to trigger teh random number in A2, and
then for the value to be copied across into C2, preventing refreshing from
affecting the number in that cell. (unless the Y in E2 dissapears and then
reappears, indicating an adjustment)

When I mentioned earlier, "without repeating" i meant that when I had tried
a macro earlier, it found the Y, pasted the value, adn then started again,
because the Y was there. I only want the Macro to run once the value arrives
in A2.

As a side note, am I approaching this from the wrong angle?
Would the result be the same if the appearance of teh random number in cell
A2 was the trigger for the macro? Or would this be activated every time the
number changed?

"Joel" wrote:

The following code must be placed in the VBA sheet where you hav ethe data.
What do you mean by doesn't repeat? You can test if cell C1 is empty and
only move the data when C1 is empty. You can clear cell C1 with a worksheet
open.

What I would dois generate the random number in the macro.




Sub worksheet_change(ByVal target As Range)

Set isect = Application.Intersect(target, Range("A1"))
If Not isect Is Nothing Then
If target.Value = "Y" Then
Range("C1").Value = Range("B1").Value
End If
End If

End Sub


"Azrael" wrote:

I have hit a brick wall with my spreadhseet, due mainly to my lack of
experience with Macros (I didn't even know they existed until last Friday).

My requirements are as follows:
I have a spreadsheet that generates a random number once certain
requirements are met in other cells. This I have managed to create with no
real problems, but every time the spreadsheet refreshes, it changes the value
of every random number in the form.

I require a Macro, that triggered by the entry of a "Y" into cell A1, runs a
one time Macro, that doesn't repeat, to copy the entry in Cell B1, and paste
the value only into cell C1.

I have tried many approaches to this issue, and none have worked in the way
I want it to.