Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to add sheets works sporadically
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to add sheets works sporadically
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 Befo=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 --------------- Jacob Skaria "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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel crashing ('sporadically') | Excel Discussion (Misc queries) | |||
Group Function Works Sporadically | Excel Discussion (Misc queries) | |||
Macros function sporadically | Excel Programming | |||
Macro for filter on protected workbook that works for all sheets, no matter what sheets are named? | Excel Programming | |||
Hiding the sheets my macro works with... | Excel Programming |