Copy Method of Worksheet Fails

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Hello fellow xls'rs:
I have a workbook that has a long list of sheetnames in sheet1!A:A. In this same workbook I have a Master Sheet . I have a procedure that copies the mastersheet and then renames it.(using the list of names from sheet1!A:A.) This procedure works quite well until about the 39 loop and all of a sudden gives me a RunTime error 1004 and says "copy method of worksheet class failed"

The line it fails on is this :
Code:
Worksheets(SalesMasterSheet).Copy after:=Worksheets(LastMadeSheet)

I've changed the order of the sheetnames in Sheet1!A:A and it still fails on the same loop ie. 39 , so it's not a typo in the file name.

I've also run the procedure on different PC's with same result..... Any ideas ?
This message was edited by nimrod on 2002-05-10 18:16
 
On 2002-05-11 22:33, TsTom wrote:
The other one is wrong...
This worked for 524 sheets copied.
Total of 527 with the default three.
I stopped it after that.
I thought you could only have 256 sheets in one workbook?

<pre>

Public Declare Function GetActiveWindow Lib _
"user32" () As Long
Public Declare Function OpenClipboard Lib _
"user32" (ByVal hwnd As Long) As Long
Public Declare Function EmptyClipboard Lib _
"user32" () As Long
Public Declare Function CloseClipboard Lib _
"user32" () As Long

Sub TryThis()
Dim cnt
For cnt = 1 To 1000
Sheets("Sheet1").Copy After:=Sheets(3)
Call OpenClipboard(GetActiveWindow())
Call EmptyClipboard
Call CloseClipboard
Next
End Sub

</pre>

Tom worksheet# is limited by the amount of
memory.....

Your code looks good and explains a few things with excel...I don't think excel
uses the windows Copy API's in fact I think
excel copies over info in it's own format
Your code sought of proves this.
I'll try it @ home now...

Thanks for the code.
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Tom,
That 255 worksheet thing is the number Excel allows you to set the default number of sheets in a new workbook at. After that you can insert them as usual.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top