Macro to add sheets works sporadically

[Steve]

I created the following to make copies of a "Master" sheet, which is hidden
at the end of the Workbook. The macro also renames the sheets as 1,2,3,etc.
It can be used to add sheets up to 25.

Sub MakeSheet()
Dim Response As String
Dim What As Integer
Response = InputBox("How many report sheets do you want to add? They
will be placed at the end. To cancel, enter 0.")
If Response = "0" Then
Exit Sub
End If
Sheets("Master").Visible = True
For cnt = 1 To Val(Response)
Sheets("Master").Select
Sheets("Master").Copy Before:=Sheets(Sheets.Count)
What = Sheets.Count - 3
ActiveSheet.Name = What
ActiveSheet.Unprotect
Range("L1").Value = ActiveSheet.Name
ActiveSheet.Protect
If What = 25 Then
Exit For
End If
Next
Sheets("Master").Visible = False
ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True
End Sub

There are extensive macros on each sheet referencing a Data sheet based on
the number stored in "L1" on each sheet as it is created. It works fine
sometimes (creating several sheets per second) but other times gives this
error part way through (i.e. some sheets created) > Run-time error '1004'.
Copy method of Worksheet class failed. This line is highlighted when you
acknowledge with "Debug" >

Sheets("Master").Copy Before:=Sheets(Sheets.Count)

Once you get the error you can't create any more unless you close and reopen
the spreadsheet. Any suggestions to prevent this error?

 

[Jacob Skaria]

Steve

Try the below and feedback..I am unable to recreate the error you mentioned..

Sub MakeSheet()
Dim varShCount As Variant, wb As Workbook
Dim wsM As Worksheet, ws As Worksheet

varShCount = (InputBox("How many report sheets do you want to " & _
"add? They will be placed at the end. To cancel, enter 0.", , 0))
If Not IsNumeric(varShCount) Then Exit Sub
If varShCount + 0 = 0 Then Exit Sub

Set wb = ActiveWorkbook
Set wsM = wb.Sheets("Master")

wsM.Visible = True
Application.ScreenUpdating = False
For cnt = 1 To CInt(varShCount)
wsM.Copy Before:=wb.Sheets(Sheets.Count)
Set ws = ActiveSheet
ws.Name = CStr(wb.Sheets.Count - 3)
ws.Unprotect
ws.Range("L1").Value = ws.Name
ws.Protect
If CInt(ws.Name) = 25 Then Exit For
Next
wsM.Visible = False
Application.ScreenUpdating = True

ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True
End Sub



If this post helps click Yes

 

[Jim Thomlinson]

It's a known bug... Check out this link.

http://support.microsoft.com/default.aspx?scid=kb;en-us;210684&Product=xlw