"Copy Method of worksheet class failed"

[Guest]

I have a procedure that copies data from a data base to a template sheet and then make a copy of the sheet which inserted at a particular point in the workbook. I say have in that it works for about 20 of the sheets that I want to copy and then stalls with the above message.

The code where it stalls at the second of the undernoted lines.

Application.DisplayAlerts = False
Sheets("Standard tenancy data").Copy Before:=Sheets("EndTenancies")
Application.DisplayAlerts = True

Any idea why this might be happening?

 

[Jim Cone]

Ron,

See if you can find something in these...

http://support.microsoft.com/?kbid=210684
Xl2000: Copying Worksheet Programmatically Causes Run-Time Error 1004
http://support.microsoft.com/?kbid=177634
Xl97: Copy Method Of Sheets Object Causes Invalid Page Fault

Regards,
Jim Cone
San Francisco, CA

I have a procedure that copies data from a data base to a template sheet and then make a copy of the sheet which inserted at a

particular point in the workbook. I say have in that it works for about 20 of the sheets that I want to copy and then stalls with
the above message.

 

[Jim Cone]

Ron,

(I think you have a stuck send key)???

I can't find the reference, but
I remember reading that the problem was fixed by MS at some point.
I assume you are using Excel 97, but do you have Service Release 2 (SR2) installed?

On my Excel 97 with SR2, I just ran the following code without problems...
'----------------------------------
Sub AddSomeSheets()
Dim N As Long
For N = 1 To 100
Worksheets(1).Copy after:=Worksheets(Sheets.Count)
Next
End Sub
'-----------------------------------------------

Regards,
Jim Cone
San Francisco, CA

Thanks Jim,

The links were helpful. It seems my problem may be as described on the Microsoft site:
This problem occurs because each time you copy the same sheet a codename is assigned to the copy, and each time the sheet is

copied, that codename becomes longer. For example, if you copy sheet1 once, the codename assigned to the sheet is sheet11. When you
copy the sheet again, it is assigned sheet111, and so on. The codename has a maximum number of about 35 characters. When this limit
is reached, Excel stops responding.

I have started to try a wordkaround as suggested there, which involves adding sheets and copying & pasting cells. Problem with

that seems to be that necessary range names are not then copied into the new sheet. Any further thoughts on this issue from you or
anyone else would be appreciated.

 

[Guest]

Thanks again Jim,

But I'm actually running Excel 2002, though some of the development was probably in 2000.

I tried running your suggestion and it stopped after 25 copies!

 

[adam12+]

I am having the same problem. I have a macro button that copies th
last project sheet and inserts it at the end of the workbook. I
varies, but after pressing the button ~25 times, I get the "Copy Metho
of Worksheet Class Failed" error.

I am using Excel 2000 version 9.0.

After looking at the Microsoft website
http://support.microsoft.com/?kbid=210684, I saw that it says "when yo
run a macro that copies worksheets, and then places the worksheets int
the *same* workbook that they originated from, you may receive... [a
error]".

Perhaps a possible solution would be to have the macro create a ne
"temporary" workbook, copy the sheet to that workbook, then move (o
copy) from that workbook back into the original workbook? Seem
inefficient and clumsy, but it is a work around. If it works, it seem
better than asking the user to save, exit, and reopen.

I'm not as adept at manipulating files (creating, switching, closing
etc.) with VB so it may take me a bit to get some code that works, bu
when I do, I'll post the results

 

[adam12+]

It turns out the solution was not as difficult as I feared!

I copied 170 sheets (of blank data) with this and it works fine s
far.

Try the following code. It takes the last sheet of a workbook an
makes a copy (at the end of the same workbook)...

'Get current workbook name
workbookname = ActiveWorkbook.Name
'Count the total number of worksheets
total=Worksheets.Count
Sheets(total).Copy 'This copies to a new work book since "Before
or "After" is omitted
'Pick the last worksheet in the new "temporary" workbook
temp = Worksheets.Count
Sheets(temp).Move after:=Workbooks(workbookname).Sheets(total

 

[Guest]

Adam! You're awesome!... I hope...

I tried out your code (modified to my situation), and presto!... I can copy
lots of sheets... But...

If I then select (e.g. as I test) all or a majority of the sheets I
created... and then choose "Delete"... Excel throws an error, and
re-starts... twice...

Hmm...

Does that Temp sheet, still exist somewhere? if so, can it be deleted/null'ed?

Cheers,
Bonzo

 

[adam12+]

Bonzo,

Glad my post could be of some help to you. Sorry to hear it sound
like you are still having problems. I'm not sure what exactly you ar
trying to do, but to answer your question, when I _move_ the recentl
copied sheets from the "temporary" workbook, that "temporary" workboo
closes (since all the sheets in that workbook are being moved). So,
don't think the "temporary" workbook is still there.

I tried to recreate what you are doing and didn't have the sam
problem. Are you choosing just the newly created (copied) worksheet
or all the worksheets? Obviously if you try to delete all th
worksheets in the workbook you may have problems, although in m
version of Excel, it just pops up a message box saying I can't do tha
and nothing gets deleted and nothing closes.

What version of Excel are you using? Perhaps that's a factor.

Good luck. I hope you can post saying you've figured it out

 

[Guest]

Adam, Bonzo,

Thanks for your postings on this thread (for which I take some
responsibility!) I posted basically the same question again yesterday
without further response. I hadn't realised that there were further postings
to my July posting, which were helpful. But I still have a problem. I have
tried copying the worksheet to a new workbook using

Sheets("Standard tenancy data").Copy

and then later on, having renamed the worksheet I used the line:

ActiveSheet.Move Before:=Workbooks(workbookname).Sheets("EndTenancies")

I found that this worked for about 12/13 copies and then the infamous 'run
time 1004' error appeared, with the slight variation in the qualification:

'Move Method of worksheet class failed' as opposed to 'Copy Method ...'

Any further insights? I'm using Excel 2002 SP3.

TIA
Ron