MrExcel Publishing
Your One Stop for Excel Tips & Solutions

copy only answer not formula


Posted by Ben on September 22, 2001 1:08 AM

i want to copy the formula answer from one cell to another, however i do not want this formula to be copied aswell. Is there any way to do this, thanx in anticipation!


Posted by Tom Urtis on September 22, 2001 1:24 AM

Do you only want to copy the result of a formula? If so, click on that cell you want to copy, then click Edit > Copy, then select your destination cell and click Edit > Paste special > Values, and click OK. Hit the Esc key to get out of copy mode.

Is this what you want or am I missing something?

Tom Urtis

Posted by ben on September 22, 2001 2:06 AM

sorry, i didnt explain myself, i have to do that in VBA! and i can only work out how to copy the formula aswell. cheerz

Posted by ben on September 22, 2001 2:08 AM

sorry didnt make that last bit clear

sorry, i didnt explain myself, i have to do that in VBA! and i can only work out how to copy the formula in VBA. cheerz

Posted by Tom Urtis on September 22, 2001 2:18 AM

No problem, hopefully this will help then

No problem, here's what a simple one would look like in VB. Modify as needed.

Sub CopyPSV_Example()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Range("C4").Copy
Range("G8").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Any help?

Tom Urtis

Posted by ben on September 22, 2001 3:39 AM

Re: No problem, hopefully this will help then

cheerz thats great, worked a treat!

Posted by Teofilo on September 23, 2001 6:08 AM

Or, more simply ........

Instead of 6 lines of code, you can just use 1 line :-

Range("G8").Value = Range("C4")