Paste Special > Values - with VBA

[wisewood]

Can anyone tell me how to do a paste special > values, using vba?

I have some cells with formulas that i need to copy, and then paste values to sort of set the value of the cell rather than it being dynamic.

Thanks.

 

[Lewiy]

You can just use:

Range("A1:E1").Value = Range("A1:E1").Value

Which will convert the range into values.

You can do an actual Paste Values command in VBA but I find this way much quicker and easier.

Hope that helps!

 

[Jonmo1]

couple methods..

method 1

range("a1:a10").Copy
range("a1:a10").PasteSpecial xlPasteValues

method 2

range("a1:a10").Value = range("a1:a10").Value

 

[VoG]

Using PasteSpecial

Range("B1").Copy
Range("A1").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False

You can't do it in one line like you can with Copy and Paste.

 

[wisewood]

Thanks guys.

After lots of trying to integrate where i need it, I finally got VoG's to work.