Automatically copy& paste special (value)

steve2115

Board Regular
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

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

Michael M

Well-known Member
Try, in the sheet module
Code:
``````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

Board Regular
Try, in the sheet module
Code:
``````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

Well-known Member
Ok, try
Code:
``````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

Board Regular
Thanks Micael that did the trick.

Regards,
Steve

Replies
4
Views
157
Replies
0
Views
358
Replies
5
Views
295
Replies
6
Views
430
Replies
0
Views
54

1,129,301
Messages
5,635,406
Members
416,856
Latest member
silentir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back