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 Befo=Sheets("EndTenancies")
Application.DisplayAlerts = True

Any idea why this might be happening?
--
TIA
Ron

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

"Ron McCormick" wrote in message ...
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 Befo=Sheets("EndTenancies")
Application.DisplayAlerts = True
Any idea why this might be happening?
TIA
Ron

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

"Ron McCormick" wrote in message ...
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.
TIA
Ron
"Jim Cone" wrote:
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

"Ron McCormick" wrote in message ...
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 Befo=Sheets("EndTenancies")
Application.DisplayAlerts = True
Any idea why this might be happening?
TIA
Ron

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!
--
TIA
Ron


"Jim Cone" wrote:

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

"Ron McCormick" wrote in message ...
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.
TIA
Ron
"Jim Cone" wrote:
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

"Ron McCormick" wrote in message ...
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 Befo=Sheets("EndTenancies")
Application.DisplayAlerts = True
Any idea why this might be happening?
TIA
Ron

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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+ " wrote:

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 so
far.

Try the following code. It takes the last sheet of a workbook and
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)


---
Message posted from http://www.ExcelForum.com/

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

--
adam12
-----------------------------------------------------------------------
adam12+'s Profile: http://www.excelforum.com/member.php...fo&userid=1341
View this thread: http://www.excelforum.com/showthread.php?threadid=23615

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 Befo=Workbooks(workbookname).Sheets("EndTenanci es")

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

"adam12+" wrote:


Bonzo,

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

I tried to recreate what you are doing and didn't have the same
problem. Are you choosing just the newly created (copied) worksheets
or all the worksheets? Obviously if you try to delete all the
worksheets in the workbook you may have problems, although in my
version of Excel, it just pops up a message box saying I can't do that
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!


--
adam12+
------------------------------------------------------------------------
adam12+'s Profile: http://www.excelforum.com/member.php...o&userid=13417
View this thread: http://www.excelforum.com/showthread...hreadid=236159