Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro, works in Excel 2002, 2003 but won't work in 2000
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" wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro, works in Excel 2002, 2003 but won't work in 2000
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" wrote in message ups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro, works in Excel 2002, 2003 but won't work in 2000
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro, works in Excel 2002, 2003 but won't work in 2000
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" wrote in message oups.com... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro, works in Excel 2002, 2003 but won't work in 2000
Yes, that's exactly what I was looking for. It works fine now. Thanks
again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Code works in Excel 2003 but won't work in Excel 2000 | Excel Programming | |||
VBA Code works in Excel 2003 but won't work in Excel 2000 | Excel Programming | |||
sendmail works in excel 2002 but not 2000 | Excel Programming | |||
Excel 2000 VBA App Now Works in Excel 2003, but not Excel 2002 | Excel Programming | |||
Hide Row macro works in 2002 but not 2003 | Excel Programming |