Please help with macro -

Posted by Quinn on January 26, 2001 7:37 PM

I have a macro that unhides a hidden master sheet,copies the sheet, and then hides the master again. It works perfectly for about 39 sheets. Then I get a Run Time Error'1004: Copy method of worksheet failed. If I save the file, close it and reopen it, It will then allow me to continue for another 20 sheets and I receive the same Message.

Is there a fix for this problem? The code I have follows:

Sub Nwall
Sheets("Wall ").Visible =True
Sheets("Wall ").Select
Sheets("Wall ").CopyBefore:=Sheets(10)
Range("B4").Select
Sheets("Wall ").Visible =False
End Sub

Should I be doing anything with the screen? I can watch it go through the steps while the macro is running -and I would rather not see it.

Thanks for any help.

Posted by Quinn on January 27, 2001 11:30 AM

Quinn,

This is a known Excel problem. It is because when you copy
'sheet1', using the copy sheet routine, it names the new sheet
'sheet11'. After Excel does this about 30 times or so, it looks
something like 'Sheet1111111111111111111111' , etc. At this point,
Excel cannot handle keep everything under control and runtime errors
occur.

The way around this is to do a 'copy cells' routine. I have done
the very thing you are doing with a hidden master, unhiding it, copying
the cells, and rehiding it. It works great.

Try something like this:

Sub CopySheet()
Application.ScreenUpdating = False
Sheets("YourSheet").Visible = True
Sheets.Add
ActiveWorkbook.Sheets("YourSheet").Cells.Copy
activesheet.Paste
ActiveWorkbook.activesheet.Select
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
End With
Sheets("YourSheet").Visible = False
End Sub

I have used this routine to create over 50 sheets in a workbook with
no problems whatsoever.

Good luck,

Garland