Paste Copy special Value

[teodormircea]

Hello

I have this macros, for Copy/Paste Special like value, but it doesn't keep the cell format .
For ex if i put =A1, and in A1 i have text mode ('000012345), when i execute the macros it doesn't keep the text format.

Sub PASTE_SPECIAL_VALUES()
Dim myAreas As Areas, myArea As Range
With Selection
On Error Resume Next
Set myAreas = .SpecialCells(12).Areas
On Error GoTo 0
If myAreas Is Nothing Then Exit Sub
For Each myArea In myAreas
myArea.Value = myArea.Value
Next
End With
End Sub

THANKS FOR YOUR HELP

[Simon Lloyd]

That code doesn't copy and paste! all it will show is the values, the code will do nothing as far as i can see because you state that for every cell in the set range the cells value should equal the cells value.....it's useless code!

[greymalkin]

why aren't you using the built in code for paste special?...sometimes I have to do a paste special formats followed by past special all to avoid getting errors.

[mdmackillop]

That code doesn't copy and paste! all it will show is the values, the code will do nothing as far as i can see because you state that for every cell in the set range the cells value should equal the cells value.....it's useless code!
Have to disagree Simon.

myArea.Value = myArea.Value


will replace formulae with the calculated value showing in the cell.

[Simon Lloyd]

I agree Malcolm that the only possible outcome would be to remove formulae but i don't see much sense in it but thats just me!

[mikerickson]

Therefore, the code doesn't need to effect the constants
Dim oneArea As Range

On Error Resume Next
With Range("g10:g20").SpecialCells(xlCellTypeVisible)
With .SpecialCells(xlCellTypeFormulas, xlTextValues)
.NumberFormat = "@"
End With
For Each oneArea In .SpecialCells(xlCellTypeFormulas).Areas
oneArea.Value = oneArea.Value
Next oneArea
End With
On Error GoTo 0Omitting the optional lines leaves some cells formatted as Text.