copy formule

[verluc]

I have a sheet with in column B1:B5000 stock prices.In each of these cells there is the same formule.Now I want a macro that automaticly makes a copy from all these formules from column B to column C and the formules in column B deleted.
Have anyone such a macro?
Thanks

 

[NateO]

How's this:

Sub theCutter()
[b1:b5000].Cut Destination:=[c1]
End Sub

Hope this helps.

 

[verluc]

On 2002-03-19 12:52, NateO wrote:
How's this:

Sub theCutter()
[b1:b5000].Cut Destination:=[c1]
End Sub

Hope this helps.

Thanks,it works, but the question was that only the formules must be deleted.The values
must be intact.
Have you a solution on this?

 

[Derek]

Hi
You could give this a try

Range("B1:B5000").Copy
Range("C1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("B1:B5000").Select
Application.CutCopyMode = False
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
Selection.ClearContents
End Sub


Or, if you mean you also want to convert the formulas in column B to values as well, then:

Range("B1:B5000").Copy
Range("C1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("B1:B5000").Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End Sub


Regards
Derek
This message was edited by Derek on 2002-03-20 01:30

 

[Dave Hawley]

Hi


Try this method

Sub Doit()
Range("C1:C5000") = Range("B1:B5000").Formula
Range("B1:B5000") = Range("B1:B5000").Value
End Sub