Simple Macro, works in Excel 2002, 2003 but won't work in 2000

[DJA]

Hello all,

I am relatively new to recording macros in excel, and I have problem
that's stumped me. I have two sheets in a workbook and I'm trying to
copy the data from one sheet and paste it to the other. Here is the
relevant portion of my code:

Sheets("Occ. Summary").Select
Range("I7").Select
Selection.Copy
Sheets("All Weeks").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

It works fine in excel 2003 and 2002, but when it's run in excel 2000,
I get a runtime 1004 error - paste special method of range class failed
at the "Selection.PasteSpecial" line.

Unfortunately, I can't just have the employees who use 2000 upgrade to
2003 (although they probably should), that isn't my decision. Any
ideas on how to modify this code so it will work in 2000?

Thanks in advance,

Dave

 

[Tom Ogilvy]

Try using

Sheets("Occ. Summary").Select
Range("I7").Select
Selection.Copy
Sheets("All Weeks").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlFormats

 

[Norman Jones]

Hi DJA,

xlPasteValuesAndNumberFormats is not avialable in xl2k.

Try :

Sheets("Occ. Summary").Range("I7").Copy
Sheets("All Weeks").Range("A3").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False


Note that I have also removed selections. These are usually unnecessary an
inefficient.

 

[DJA]

Thanks for the tips, guys. I think I'm on the right track. One more
question though, is there a way to paste the number formats in Excel
2000? I have some cells in percentage and currency formats, and I'd
like paste those to the second sheet, but I don't want to paste the
cell formats, i.e. font size, bold etc.

Thanks,

Dave

 

[Tom Ogilvy]

Sheets("All Weeks").Range("A3").Value = _
Sheets("Occ. Summary").Range("I7").Value
Sheets("All Weeks").Range("A3").NumberFormat = _
Sheets("Occ. Summary").Range("I7").NumberFormat

 

[DJA]

Yes, that's exactly what I was looking for. It works fine now. Thanks
again.