xlPasteValuesAndNumberFormats

[fernando]

Hello,

I am pasting data with a macro using
xlPasteValuesAndNumberFormats. In computers that have
excel 2000, this constant is not recognized. I looked it
up in the "object examinator"(F2), and it is part of the
class XlPasteType. However, in excel 2000 this class
doesn't have the xlPasteValuesAndNumberFormats constant.
Is there a way to add this constant without having to
install a newer version of excel?

Thanks for your help
Fernando E

 

[Tom Ogilvy]

range("A1").Pastespecial xlvalues
range("A1").Pastespecial xlFormats

 

[Rob Bovey]

Hi Fernando,

This paste special option was first introduced in Excel 2002, so it will
not work in Excel 2000. There really isn't any completely equivalent way to
do this operation in Excel 2000 either. The closest would be to perform to
paste special operations in a row, as shown below, one to paste values and
the other to paste formats. This will work in all versions of Excel, but it
will paste all formats, not just number formats.

Sheet1.Range("A1").Copy
Sheet1.Range("A2").PasteSpecial Paste:=xlValues
Sheet1.Range("A2").PasteSpecial Paste:=xlFormats

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Tom Ogilvy]

or

Sub Tester1()
Dim srcrng As Range, cell As Range
Set srcrng = Range("F3:G22")
srcrng.Copy
Range("A1").PasteSpecial xlValues
For Each cell In srcrng
Range("A1").Offset( _
cell.Row - srcrng(1).Row, _
cell.Column - srcrng(1).Column) _
.NumberFormat = cell.NumberFormat
Next
End Sub