Macro for a one time triggered copy of cell value

[Azrael]

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.

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
If UCase(Target.Value) = "Y" Then
Application.EnableEvents = False
Range("B1").Copy
Range("C1").PasteSpecial Paste:=xlPasteValues
Application.EnableEvents = True
End If
End If
End Sub

Mike

 

[Joel]

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]

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]

Remove the number of C1 then run this macro. I'm not using the random number
in B1.

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 = Rnd()
End If
End If

End Sub

 

[Azrael]

I made a tweak and it works perfectly!! Now I have one question to follow,
which may seem mediocre, nut as I said, I'm new to this.

This is working perfectly for one row. Now I need to create a similar macro
for each row in the form.(up to 50) To ensure that they won't interfere with
each other. is there some way of saving the macro's to the form under a
custom name for each row, because I find that when I attempt to create a
macro for the next row, it states that I have an ambigous name in
worksheet_change, and if I change it, the macro won't trigger properly.

Is this only because it is used in the same window?

 

[Joel]

See if this works. I change the Intersect Range and I'm using Offset from
the target location to place the random number

Sub worksheet_change(ByVal target As Range)

Set isect = Application.Intersect(target, Range("A1:A50"))
If Not isect Is Nothing Then
If target.Value = "Y" Then
Target.Offset(0,2).Value = Rnd()
End If
End If

End Sub

 

[Azrael]

Thank you both for your help in this matter. Using a combination of your
suggestions, and other things I had found lying about in the forum, I managed
to construct the following macro script which does exactly what I wanted .


Sub worksheet_change(byval target as range)

set isect = application.intersect(target, range("A1:A51"))
If not isect is nothing then
Target.offset(0, 1).copy
Target.offset(0, 2).pastespecial xlpastevalues
application.cutcopymode = false
end if
end if
end sub

 

[Joel]

Not sure why you want to use the worksheet randomize function. But that is
ok. He is a simple change

from:
Target.offset(0, 1).copy
Target.offset(0, 2).pastespecial xlpastevalues

to:

Target.offset(0, 2).value = Target.offset(0, 1).value