HELP!! Excel 2000 Copy of worksheet class failed run time Error 1004

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

Please can someone help me...

I am using excel 2000 and have write a macro in VBA to copy a
worksheet and then rename the copied sheet. My problem is that are
loop 23 time I get the error Copy of worksheet clas failed Run time
Error 1004.

I have looked at some example were other people had the same problem
but when I use the code i must be doing something wrong.

My code reads

Sheets("ReconMaster").Select
Sheets("ReconMaster").Copy After:=Sheets(7)
Sheets("ReconMaster (2)").Select
Sheets("ReconMaster (2)").Name = strSheetN

Once the sheet is copied I rename based on an Emplyee number. I have
about 100 sheets to create.

Can someone help me get over this problem.
 
Maybe this will help?

Sub ReconMaster()

Dim i As Integer
Dim j As Integer

Do
i = (i + 1)
Sheets("ReconMaster").Copy After:=ActiveSheet
j = InputBox("Enter employee number")
If j = 0 Then
ActiveSheet.Delete
Exit Sub
End If
ActiveSheet.Name = j
Loop Until i = 100
End Sub
 
Back
Top