Automatically copy& paste special (value)

[steve2115]

Need help on vb script to automatically copy & paste the value from a calculated field into a new field.


Cell C5 is a calculated field, formula is (=SUM($E$9:$E$60)/C1) This field is calculating % complete.

Cell C6 is also a calculated field, formula is =IF(C5<90%,ROUNDDOWN(NETWORKDAYS(A6,A7,0),0)/5,COUNTIFS(F9:F60,">0")). This field is "counting" the # of weeks it takes to complete.

Cell F6 formula is =IF(C5>=90%,C6,"") Field displays C6 (# of weeks) when C5 is > or = to 90%, otherwise it is blank.

What I need to do is, copy & paste the "value" of C6 (# of weeks) into cell F6 when cell C5 is > or = to 90% and "freeze" or hold the value.


Thanks

 

[Michael M]

Try, in the sheet module

Sub Worksheet_change(ByVal target As Range)
Set target = Range("C5")
If Intersect(target, Range("C5")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If target.Value >= 0.9 Then Range("F6").Value = Range("C6").Value
Application.EnableEvents = True
End Sub

 

[steve2115]

Try, in the sheet module

Sub Worksheet_change(ByVal target As Range)
Set target = Range("C5")
If Intersect(target, Range("C5")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If target.Value >= 0.9 Then Range("F6").Value = Range("C6").Value
Application.EnableEvents = True
End Sub

Thanks Michael this is great. Never would have got it myself. Code gets me the value, however I need to "freeze" it. In other words, once C5 hits 90% I do not want F6 to update.

 

[Michael M]

Ok, try

Sub Worksheet_change(ByVal target As Range)
Set target = Range("C5")
If Range("F6").Value > 0 Then Exit Sub
If Intersect(target, Range("C5")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If target.Value >= 0.9 Then Range("F6").Value = Range("C6").Value
Application.EnableEvents = True
End Sub

 

[steve2115]

Thanks Micael that did the trick.

Regards,
Steve