Use VBA worksheet event to copy cell value and paste in another if condition met

[spena129]

Hi All,

I am sure this has been asked/solved before, but I cannot seem to figure it out nor can I find the answer in a forum. I am trying to write a macro on a specific worksheet where a cell value will automatically be copied and pasted in another cell if the original cell's result meets a condition (>0).

Example: Cell A19 has a formula that calculates based on input from another cell. It is set at 0. If A19 changes (can only increase), then the result would be copied and value pasted in cell C15.

I know that is most likely will be a worksheet change/calculation event whereby A19 is selected, copied, and values pasted.


Thanks in Advance!

 

[Luke M]

How about this? From what you said, don't really need to know the old value of A19, just need to compare it to C15 (since C15 should already have the old value in it).

[pre]

Private Sub Worksheet_Calculate()
Dim newValue As Long
Dim oldValue As Long
newValue = Range("A19").Value
oldValue = Range("C15").Value

If newValue > oldValue And newValue > 0 Then
'Turn this off so we don't trigger an endless loop
'of worksheet events
Application.EnableEvents = False
Range("C15").Value = newValue
Application.EnableEvents = True
End If
End Sub

[/pre]

 

[spena129]

Luke,

I never thanked you for the help. Works like a charm.


Thanks a bunch,

Steve