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

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

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

--
Regards,
Tom Ogilvy




"DJA" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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.

---
Regards,
Norman



"DJA" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

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

--
Regards,
Tom Ogilvy


"DJA" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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