problem with macro for creating new sheets


Posted by Tim on May 02, 2000 5:12 PM

I have the following macro in a workbook designed to build a workbook around a team of agents. The problem is that the macro will only create about 13 new sheets before it gives the following this error. "Copy method of Worksheet class failed" and debug shows the problem is in the copy line.

Anyone have any insight into why this is happening. (it has the same problem after changing memory settings and rebooting the computer)

Sub CreateSheets()
ActiveWorkbook.Sheets("Names").Select
FinalRow = Range("A50").End(xlUp).Row
For x = 2 To FinalRow
LastSheet = ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets("Names").Select
Agent = Range("A" & x).Value
ActiveWorkbook.Sheets("LiveTech").Copy After:=ActiveWorkbook.Sheets(LastSheet)
ActiveWorkbook.Sheets(LastSheet + 1).Name = Agent
ActiveWorkbook.Sheets(Agent).Select
Range("T4").Value = Agent

Next x

End Sub

Posted by Ivan Moala on May 03, 2000 3:20 AM


Tim
Have a look @ this article.
I think this may help you?
http://support.microsoft.com/support/kb/articles/Q177/6/34.ASP?LN=EN-US&SD=gn&FR=0

Ivan



Posted by Tim on May 04, 2000 6:22 PM

Thanks Ivan.

P.S. That kind of bites. Gotta hate known issues.