Automatically copy& paste special (value)

steve2115

Board Regular
Joined
Mar 17, 2014
Messages
79
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
Joined
Oct 27, 2005
Messages
20,111
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
Joined
Mar 17, 2014
Messages
79
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
Joined
Oct 27, 2005
Messages
20,111
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top