Removing Excel cell formulae but retaining the values

[Nigel Barton]

I want to copy ranges to new worksheets retaining all data (including values,
formatting, comments) but not the formulae. There does not seem to be a
pastespecial option for this. As a workaround I have used option xlPasteAll
and then iterated thtrough the cells of the new ranges assigning their value
to the value property. The result is as desired but I was wondering if there
might be a neater solution.

 

[Jim Cone]

Sub Demo()
Dim lngRows As Long
Dim lngCols As Long

With Worksheets(1).Range("StartRange")
lngRows = .Rows.Count
lngCols = .Columns.Count
.Copy
End With

With Worksheets(2).Range("D14").Resize(lngRows, lngCols)
.PasteSpecial
.PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
End Sub
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/PrimitiveSoftware

..
..
..

"Nigel Barton"
<[email protected]>
wrote in message
I want to copy ranges to new worksheets retaining all data (including values,
formatting, comments) but not the formulae. There does not seem to be a
pastespecial option for this. As a workaround I have used option xlPasteAll
and then iterated thtrough the cells of the new ranges assigning their value
to the value property. The result is as desired but I was wondering if there
might be a neater solution.