Paste Special

[rhombus4]

I have a formula in Cell A1 which relates to some code, however because it is a formula the code doesnt run, however If I type the answer in cell A1 the code works.

What I need is for the answer in cell A1 to appear in a different cell e.g. B1 but just appear as the value and not the formula i.e. =A1 then i can change my code to look for B1

 

[stnkynts]

If you are inquiring with regards to VBA then like this:

Range("A1").Copy
Range("B1").PasteSpecial xlPasteValues

 

[Smitty]

You can use a Calculate event:

Private Sub Worksheet_Calculate()
Range("B1").Value = Range("A1").Value
End Sub

HTH,

 

[rhombus4]

If you are inquiring with regards to VBA then like this:

Range("A1").Copy
Range("B1").PasteSpecial xlPasteValues

but how would i enter that in vba so when the formula is entered in A1 the result would appea in B1 automatically

 

[rhombus4]

You can use a Calculate event:

Private Sub Worksheet_Calculate()
Range("B1").Value = Range("A1").Value
End Sub

HTH,

Thankyou