Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Copy fails (xl2003)
I'm getting a very strange error.
This line generates an error: Worksheets("VBA_BlankRecord").Copy after:=Worksheets(Worksheets.Count) as does this one: Worksheets("VBA_BlankRecord").Copy The error is runtime error 1004, "copy method of worksheet cllass failed" (I could figure that part out myself!). In security, I have Tust Access to the Visual Basic Project checked. Just to make sure I hadn't misspelled the name, I tried this line: Set ws = Worksheets("VBA_BlankRecord") and it works fine. If I try an earlier version of the workbook, the lines above work, even though I haven't changed anything this procedure, or the worksheet it's copying, since that earlier version. Darren |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Copy fails (xl2003)
Try this and see what happens:
Dim myWS as worksheet Set myWS = nothing on error resume next Set myWS = Worksheets("VBA_BlankRecord") on error goto 0 if not myWS is nothing then Do whatever you want to do. else Debug.print "myWS is nothing" end if "Darren Hill" wrote: I'm getting a very strange error. This line generates an error: Worksheets("VBA_BlankRecord").Copy after:=Worksheets(Worksheets.Count) as does this one: Worksheets("VBA_BlankRecord").Copy The error is runtime error 1004, "copy method of worksheet cllass failed" (I could figure that part out myself!). In security, I have Tust Access to the Visual Basic Project checked. Just to make sure I hadn't misspelled the name, I tried this line: Set ws = Worksheets("VBA_BlankRecord") and it works fine. If I try an earlier version of the workbook, the lines above work, even though I haven't changed anything this procedure, or the worksheet it's copying, since that earlier version. Darren |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Copy fails (xl2003)
Is there anything else that might be named the same? Check the declarations
and UserForms. Sometimes I inadvertantly use the same variable name for different items and screw everything up until I find my goof. But at my age, I am lucky to still be doing this stuff. "Darren Hill" wrote: I'm getting a very strange error. This line generates an error: Worksheets("VBA_BlankRecord").Copy after:=Worksheets(Worksheets.Count) as does this one: Worksheets("VBA_BlankRecord").Copy The error is runtime error 1004, "copy method of worksheet cllass failed" (I could figure that part out myself!). In security, I have Tust Access to the Visual Basic Project checked. Just to make sure I hadn't misspelled the name, I tried this line: Set ws = Worksheets("VBA_BlankRecord") and it works fine. If I try an earlier version of the workbook, the lines above work, even though I haven't changed anything this procedure, or the worksheet it's copying, since that earlier version. Darren |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Copy fails (xl2003)
Is the sheet hidden?
Darren Hill wrote: I'm getting a very strange error. This line generates an error: Worksheets("VBA_BlankRecord").Copy after:=Worksheets(Worksheets.Count) as does this one: Worksheets("VBA_BlankRecord").Copy The error is runtime error 1004, "copy method of worksheet cllass failed" (I could figure that part out myself!). In security, I have Tust Access to the Visual Basic Project checked. Just to make sure I hadn't misspelled the name, I tried this line: Set ws = Worksheets("VBA_BlankRecord") and it works fine. If I try an earlier version of the workbook, the lines above work, even though I haven't changed anything this procedure, or the worksheet it's copying, since that earlier version. Darren -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Copy fails (xl2003)
Yes, it is. Would that make a difference?
It works on the backup version, while hidden. Darren On Sun, 01 Apr 2007 02:23:23 +0100, Dave Peterson wrote: Is the sheet hidden? Darren Hill wrote: I'm getting a very strange error. This line generates an error: Worksheets("VBA_BlankRecord").Copy after:=Worksheets(Worksheets.Count) as does this one: Worksheets("VBA_BlankRecord").Copy The error is runtime error 1004, "copy method of worksheet cllass failed" (I could figure that part out myself!). In security, I have Tust Access to the Visual Basic Project checked. Just to make sure I hadn't misspelled the name, I tried this line: Set ws = Worksheets("VBA_BlankRecord") and it works fine. If I try an earlier version of the workbook, the lines above work, even though I haven't changed anything this procedure, or the worksheet it's copying, since that earlier version. Darren -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Copy fails (xl2003)
Thanks, Dave.
I tested, by having the code make the sheet visible, perform the copy, then hide it again, and it works. So, being hidden seems to stops the copy on some versions of the workbook and not others. Can you explain why this is? Darren On Sun, 01 Apr 2007 02:23:23 +0100, Dave Peterson wrote: Is the sheet hidden? Darren Hill wrote: I'm getting a very strange error. This line generates an error: Worksheets("VBA_BlankRecord").Copy after:=Worksheets(Worksheets.Count) as does this one: Worksheets("VBA_BlankRecord").Copy The error is runtime error 1004, "copy method of worksheet cllass failed" (I could figure that part out myself!). In security, I have Tust Access to the Visual Basic Project checked. Just to make sure I hadn't misspelled the name, I tried this line: Set ws = Worksheets("VBA_BlankRecord") and it works fine. If I try an earlier version of the workbook, the lines above work, even though I haven't changed anything this procedure, or the worksheet it's copying, since that earlier version. Darren -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Copy fails (xl2003)
I had trouble if I was copying any non-visible sheet to a new workbook. But in
any workbook, one sheet has to be visible--so that makes sense to me. I didn't have any trouble copying a sheet that was xlsheethidden (within the same workbook). I had trouble with sheets that were xlsheetveryhidden (within the same workbook). I have no idea why there's a difference between the behaviors in the last two. From my standpoint, there just is <vbg. Darren Hill wrote: Thanks, Dave. I tested, by having the code make the sheet visible, perform the copy, then hide it again, and it works. So, being hidden seems to stops the copy on some versions of the workbook and not others. Can you explain why this is? Darren On Sun, 01 Apr 2007 02:23:23 +0100, Dave Peterson wrote: Is the sheet hidden? Darren Hill wrote: I'm getting a very strange error. This line generates an error: Worksheets("VBA_BlankRecord").Copy after:=Worksheets(Worksheets.Count) as does this one: Worksheets("VBA_BlankRecord").Copy The error is runtime error 1004, "copy method of worksheet cllass failed" (I could figure that part out myself!). In security, I have Tust Access to the Visual Basic Project checked. Just to make sure I hadn't misspelled the name, I tried this line: Set ws = Worksheets("VBA_BlankRecord") and it works fine. If I try an earlier version of the workbook, the lines above work, even though I haven't changed anything this procedure, or the worksheet it's copying, since that earlier version. Darren -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Copy fails (xl2003)
My sheet was very hidden, too.
I can live with it now that I know about it. Thanks again for your help. Darren On Sun, 01 Apr 2007 13:24:22 +0100, Dave Peterson wrote: I had trouble if I was copying any non-visible sheet to a new workbook.. But in any workbook, one sheet has to be visible--so that makes sense to me. I didn't have any trouble copying a sheet that was xlsheethidden (within the same workbook). I had trouble with sheets that were xlsheetveryhidden (within the same workbook). I have no idea why there's a difference between the behaviors in the last two. From my standpoint, there just is <vbg. Darren Hill wrote: Thanks, Dave. I tested, by having the code make the sheet visible, perform the copy, then hide it again, and it works. So, being hidden seems to stops the copy on some versions of the workbook and not others. Can you explain why this is? Darren On Sun, 01 Apr 2007 02:23:23 +0100, Dave Peterson wrote: Is the sheet hidden? Darren Hill wrote: I'm getting a very strange error. This line generates an error: Worksheets("VBA_BlankRecord").Copy after:=Worksheets(Worksheets.Count) as does this one: Worksheets("VBA_BlankRecord").Copy The error is runtime error 1004, "copy method of worksheet cllass failed" (I could figure that part out myself!). In security, I have Tust Access to the Visual Basic Project checked. Just to make sure I hadn't misspelled the name, I tried this line: Set ws = Worksheets("VBA_BlankRecord") and it works fine. If I try an earlier version of the workbook, the lines above work, even though I haven't changed anything this procedure, or the worksheet it's copying, since that earlier version. Darren -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP! Macro FAILS when I copy the worksheet... | Excel Programming | |||
Copy Destination fails | Excel Programming | |||
Populating Userform ComboBox with worksheet names in XL2003 | Excel Programming | |||
Copy to another sheet fails | Excel Programming | |||
Copy method fails in IIS | Excel Programming |