I wrote a Macro using the Record Macro function in Excel. The Macro
essentially Copies a Worksheet, Renames it, Copies the New Worksheet, Renames
it, etc. until I have looped a predetermined number of times.

The problem I am having is that the first time it works great. However, if
I delete the newly created worksheets and run the Macro again it only loops
for a few iterations and then I get a "Run Time Error '1004' Copy Method of
Worksheet Class Failed.

THe more distrubing problem is that I based this Macro on the Edit - Move
or Copy Worksheet command. After I reset the Macro, Edit - Move or Copy
Worksheet command in Excel no longer works. I have to exit Excel completely
and restart. The Edit - Move or Copy worksheet command works and I am able to
run the macro. However, if I delete the newly created worksheets and try to
rerun, the problems happens again.

Here is the Code I am using.

Sub CreateTMSheets()
Dim RowCount As Integer
Dim i As Integer
RowCount = Range("NortheastData").Rows.Count
' The number of rows will be 28 for the Northeast.
For i = 1 To RowCount - 1
Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.Copy After:=Worksheets(3 + i)
Application.Run "OnGenericSetSheetActive"
Range("X4").Select
Range("A1").Value = ActiveCell.Offset(i, 0)
ActiveSheet.Name = Left(Cells(1, 1), 4)
Range("A1").Select
Next i
End Sub

Any help anyone can provide will be greatly appreciated. I can't figure
out why this Macro is effecting the Copy functionality within Excel.

--
Shade

Hi, in my opinion, the actions you performed, after you started recording the
macros, might have affected the other operations.

"shade" wrote:

I wrote a Macro using the Record Macro function in Excel. The Macro
essentially Copies a Worksheet, Renames it, Copies the New Worksheet, Renames
it, etc. until I have looped a predetermined number of times.

The problem I am having is that the first time it works great. However, if
I delete the newly created worksheets and run the Macro again it only loops
for a few iterations and then I get a "Run Time Error '1004' Copy Method of
Worksheet Class Failed.

THe more distrubing problem is that I based this Macro on the Edit - Move
or Copy Worksheet command. After I reset the Macro, Edit - Move or Copy
Worksheet command in Excel no longer works. I have to exit Excel completely
and restart. The Edit - Move or Copy worksheet command works and I am able to
run the macro. However, if I delete the newly created worksheets and try to
rerun, the problems happens again.

Here is the Code I am using.

Sub CreateTMSheets()
Dim RowCount As Integer
Dim i As Integer
RowCount = Range("NortheastData").Rows.Count
' The number of rows will be 28 for the Northeast.
For i = 1 To RowCount - 1
Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.Copy After:=Worksheets(3 + i)
Application.Run "OnGenericSetSheetActive"
Range("X4").Select
Range("A1").Value = ActiveCell.Offset(i, 0)
ActiveSheet.Name = Left(Cells(1, 1), 4)
Range("A1").Select
Next i
End Sub

Any help anyone can provide will be greatly appreciated. I can't figure
out why this Macro is effecting the Copy functionality within Excel.

--
Shade