Paste Special Value - Lost formula in Macro

[Guest]

In Excel 2003 when I use the Paste Special (Value only) within a Macro, the
calculated value in the source cell is displayed yet the cell formula is
lost. This does not occur when performed outside of the macro.

Is there a known fix or workaround Can't find one!

 

[Nick Hodge]

Jeff

I'm not understanding this. Paste special...>Values will lose the formula in
*all* cases, macro or through the UI

Sub Test()
Range("A1").Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
End Sub

Will take a formula in A1 and paste the value that is produced by the
formula and replace with it's value in the same cell.

This is equivalent to Edit>Copy and then Edit>Paste special...>Values in the
UI to the same cell

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Guest]

Thanks Nick.

Let me clarify. I am loosing the formula in A1 when I paste special the
value only to A2. A1 and A2 display the value but the A1 formula is gone
from A1.

 

[Nick Hodge]

Jeff

Can you post the code?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Guest]

Nick,

Well perseverence has paid off, at your expense.
I found an error in my work.

Sorry, and thanks!