Sub PasteVal()
Selection.Copy
Selection.PasteSpecial xlPasteValues
End Sub
It depends on what you mean by "real time". Under what circumstances do you want the values to change?
This code will do it, but is triggered manually.
Select the range you want to convert to values and run this code.
Code:Sub PasteVal() Selection.Copy Selection.PasteSpecial xlPasteValues End Sub
It depends on what you mean by "real time". Under what circumstances do you want the values to change?
This code will do it, but is triggered manually.
Select the range you want to convert to values and run this code.
Code:Sub PasteVal() Selection.Copy Selection.PasteSpecial xlPasteValues End Sub
Sub CheckTime()
<comm style="color: rgb(0, 232, 15);">'if B2 is a formula</comm>
If Range("B2").HasFormula Then
<comm style="color: rgb(0, 232, 15);">'if A2 time is in the past</comm>
If Range("A2") <= Now() Then
<comm style="color: rgb(0, 232, 15);">'copy B2</comm>
Range("B2").Copy
<comm style="color: rgb(0, 232, 15);">'paste it as values</comm>
Range("B2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Else
<comm style="color: rgb(0, 232, 15);">'if A2 is in the future, run again in 30 seconds</comm>
Application.OnTime Now + TimeValue("00:00:30"), "CheckTime"
End If
End If
End Sub
Private Sub Workbook_Open()
Run CheckTime
End Sub
Yes, as I said, that code is triggered manually. I needed more information about what you wanted to do.
This code will look every 30 seconds at the B2 cell to see if it's a formula. If it is, it checks the time in A2. If A2 is before than the current time, it copies the formula an pastes it as a value. If not, it calls the routine again in 30 seconds.
Put this in a module.
Unless you want to run it manually the first time, you'll need to put code in to run it the first time when the workbook is open. After that it will run itself until it is no longer a formulaCode:Sub CheckTime() <comm style="color: rgb(0, 232, 15);">'if B2 is a formula</comm> If Range("B2").HasFormula Then <comm style="color: rgb(0, 232, 15);">'if A2 time is in the past</comm> If Range("A2") <= Now() Then <comm style="color: rgb(0, 232, 15);">'copy B2</comm> Range("B2").Copy <comm style="color: rgb(0, 232, 15);">'paste it as values</comm> Range("B2").PasteSpecial xlPasteValues Application.CutCopyMode = False Else <comm style="color: rgb(0, 232, 15);">'if A2 is in the future, run again in 30 seconds</comm> Application.OnTime Now + TimeValue("00:00:30"), "CheckTime" End If End If End Sub
Put this in the This Workbook code page.
Code:Private Sub Workbook_Open() Run CheckTime End Sub