Copy Method of Worksheet Fails

[Nimrod]

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 :

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

 

[NateO]

Plus Nimrod's from Vancouver! A great town, only a few hours from Chilliwack, the green heart of BC (I'm a Rosedale jr. high alum, where I hold the school record for fastest mile (4 minutes, 54 seconds)).

I think you can copy if you refer to the index number. I assume LastMadeSheet is defined as a worksheet, so you won't need to set this as I have, just use LastMadeSheet.index. The second line is for Mark O'Brien.

Sub cpy()
'Macro recorded by Nate
Dim n As Worksheet
Set n = sheets(LastMadeSheet)
    Sheets(SalesMasterSheet).Copy After:=Sheets(n.Index)
End Sub

Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

This message was edited by NateO on 2002-05-11 12:05

 

[Nimrod]

Hi NateO !
Thanks for your suggestion(s). Unfortunately my problem hasn't gone away. If you have any ideas they would be appreciated ... I'm running out of ideas and would rather be outside on this sunny day instead of finishing this project !

Again thanks for all the help

 

[NateO]

Show your code, want to see how you're working with LastSheet.....This methodology will work.

 

[Nimrod]

Private Sub CopyMasterSheet()

    'THIS IS SO YOU DON'T HAVE POP-UP ALERTS FOR DELETES ETC
    Application.DisplayAlerts = False
    
    '  you try to delete a sheet that does exist it resumes on line just after the errro line
    On Error Resume Next
    
    ' delete the sheet if it exists
    Worksheets(SheetInfo.Label).Delete
   
    'copy
    'Worksheets(SalesMasterSheet).Copy after:=Worksheets(SetUpSheetName)
    Worksheets(SalesMasterSheet).Copy After:=Worksheets(LastMadeSheet)
    
    'rename
    ActiveSheet.Name = SheetInfo.Label
    LastMadeSheet = SheetInfo.Label

Application.DisplayAlerts = True

End Sub

 

[NateO]

You're using global variables (I hope), that I can't see. Otherwise you need to refer to sheet names with quotes, i.e., sheets("sheet1"). But I'll guess. I haven't tested this, but give her a whirl:

Private Sub CopyMasterSheet()
Dim n As Worksheet
Set n = Worksheets(LastMadeSheet)
    'THIS IS SO YOU DON'T HAVE POP-UP ALERTS FOR DELETES ETC
    Application.DisplayAlerts = False
    
    '  you try to delete a sheet that does exist it resumes on line just after the errro line
    On Error Resume Next
    
    ' delete the sheet if it exists
    Worksheets(SheetInfo.Label).Delete
   
    'copy
    'Worksheets(SalesMasterSheet).Copy after:=Worksheets(SetUpSheetName)
    Worksheets(SalesMasterSheet).Copy After:=Sheets(n.Index)
    
    'rename
    ActiveSheet.Name = SheetInfo.Label
    LastMadeSheet = SheetInfo.Label

Application.DisplayAlerts = True

End Sub

Also, I think
LastMadeSheet = SheetInfo.Label
is going to fail, if LastMadeSheet is a variable, I think you'll need to set it, like line 3 above.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

This message was edited by NateO on 2002-05-11 12:45

 

[Ivan F Moala]

The workaround I gave was for Xl97
Have a look here for the workaround in xl2K

http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q210684

 

[Nimrod]

Ivan, NateO:
Thanks for all the help. Yes it seems to be a limitation of Excel when copiing sheets that have links. I've taken the source sheet and placed it in new workbook with the following code:

For cnt = 1 To 100
    
    Sheets("MASTER").Select
    Sheets("MASTER").Copy After:=Sheets(3)
next

Sure enough even with this minimal code in the workbook the copy process always stops at loop 46!
And as the document Ivan has pointed me to says, the only way to copy more is to close the workbook.
So I guess I'm going to have to overhaul my program. Again thanks for all the help you've given me.

 

[NateO]

You actually don't need to need to select the sheets. I got this to loop through 100, (although it is xl2000).

For cnt = 1 To 100
    Sheets("MASTER").Copy After:=Sheets(3)
next

Might be worth a shot.


_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

This message was edited by NateO on 2002-05-11 17:44

 

[Legacy 98055]

This message was edited by TsTom on 2002-05-11 22:34

 

[Legacy 98055]

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>