Worksheet Copy fails (xl2003)

[Darren Hill]

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

 

[Guest]

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

 

[Guest]

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.

 

[Dave Peterson]

Is the sheet hidden?

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

 

[Darren Hill]

Yes, it is. Would that make a difference?
It works on the backup version, while hidden.

Darren

 

[Darren Hill]

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

 

[Dave Peterson]

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]

My sheet was very hidden, too.

I can live with it now that I know about it.
Thanks again for your help.

Darren