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