View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
Jim Thomlinson Jim Thomlinson is offline
external usenet poster
 
Posts: 5,939
Default Macro to add sheets works sporadically

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

http://support.microsoft.com/default...84&Product=xlw
--
HTH...

Jim Thomlinson


"Steve" wrote:

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 Befo=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 Befo=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?