Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
error message
I have a macro that reads data from a sheet, and generate sheets and
workbooks. The number of data rows in the sheet are approximately 350. Depending on the data value, it generates new workbooks composed of several sheets. While I use For count = 101 to ~ next , When the count value reaches about 250 (this is not exact), the macro generates an error, saying "Run-time error: 1004, copy method of worksheet class failed" (yes. the error occurs when the macro copies a sheet) What I don't understand is, however, the place that generates the error worked fine before the count reached the value (about 250). In other words, it worked fine for 250 times. Then suddenly generates an error. Once I restart Excel and start the macro beginning the count value when it stopped, it runs well again for some amount of iteration. Is there any reason for that? I have included the code below, hoping it doesn't take too much space. thanks Public Const start_section As Integer = 101 Public Const end_section As Integer = 411 Public Const Anal_Sheet As String = "ACI" Public Const My_Dir As String = "D:\Thesis\Analysis\Excel\Standards\" Dim i As Integer Sub ACI() Application.EnableEvents = False Application.ScreenUpdating = False For i = start_section To end_section 'do nothing for the same section If Sheets("DB").Cells(i + 6, 7) = Sheets("DB").Cells(i + 5, 7) And _ Sheets("DB").Cells(i + 6, 15) = Sheets("DB").Cells(i + 5, 15) Then ' do nothing Else 'do analysis if different section Sheets(Anal_Sheet).Activate Sheets(Anal_Sheet).Range("B5").Value = i 'change section Call GoalSeek 'find M, @P=0 Call Copy_and_Sort 'copy temp data (under chart) to the place and sort 'generate a new Sheet and copy data Sheets("temp_form").Copy After:=Sheets(Sheets.Count) Set new_sheet = Sheets(Sheets.Count) new_sheet.Name = i new_sheet.Range("b9:C9", Range("b9:c9").End(xlDown)).ClearContents With new_sheet .Range("A1") = i .Range("B1") = Sheets("DB").Range("D1").Offset(i + 6, 0) .Range("B4:D4") = Sheets(Anal_Sheet).Range("C5:E5").Value ' b, t, KL .Range("E4") = Sheets(Anal_Sheet).Range("H5").Value ' Fy .Range("F4") = Sheets(Anal_Sheet).Range("M5").Value ' f'c .Range("G4") = Sheets(Anal_Sheet).Range("B17").Value ' KL/r .Range("H4") = Sheets(Anal_Sheet).Range("D17").Value ' short column .Range("D9:F21") = Sheets(Anal_Sheet).Range("D23:F35").Value ' copy P, M from ACI to here End With End If Sheets(Sheets.Count).Range("B65535").End(xlUp).Off set(1, 0) = Sheets("DB").Cells(i + 6, 16).Value Sheets(Sheets.Count).Range("C65535").End(xlUp).Off set(1, 0) = Sheets("DB").Cells(i + 6, 19).Value If Sheets("DB").Cells(i + 6, 4) = Sheets("DB").Cells(i + 7, 4) Then ' do nothing Else: Call MoveSheets End If Workbooks("us standards").Save Next i Application.ScreenUpdating = False Application.EnableEvents = True End Sub Sub GoalSeek() 'goal seek Sheets(Anal_Sheet).Range("AO36").GoalSeek Goal:=0, ChangingCell:=Range("X12") 'copy the goal seek result Sheets(Anal_Sheet).Range("N30") = Sheets(Anal_Sheet).Range("x12").Value Sheets(Anal_Sheet).Range("X12") = 8 End Sub Sub Copy_and_Sort() 'copy temp data (under the chart) to the correct place Sheets(Anal_Sheet).Range("B24:E34") = Sheets(Anal_Sheet).Range("N20:Q30").Value Sheets(Anal_Sheet).Range("B24:E34").Sort Key1:=Sheets(Anal_Sheet).Range("B24"), Order1:=xlAscending End Sub Sub MoveSheets() Dim shtArray() As Integer Dim shts As Integer Sheets(Anal_Sheet).Activate For shts = Sheets("temp_form").Index + 1 To Sheets.Count ReDim Preserve shtArray(Sheets("temp_form").Index + 1 To shts) shtArray(shts) = shts Next shts Sheets(shtArray).Move ActiveWorkbook.SaveAs Filename:=My_Dir & i ActiveWorkbook.Close End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
error message
Hi,
I have runned in to this problem many times with at my clients PC. My answer is: The memory is full. Don't know if this is the right answer but I'm pretty sure... /Nicke -----Original Message----- I have a macro that reads data from a sheet, and generate sheets and workbooks. The number of data rows in the sheet are approximately 350. Depending on the data value, it generates new workbooks composed of several sheets. While I use For count = 101 to ~ next , When the count value reaches about 250 (this is not exact), the macro generates an error, saying "Run-time error: 1004, copy method of worksheet class failed" (yes. the error occurs when the macro copies a sheet) What I don't understand is, however, the place that generates the error worked fine before the count reached the value (about 250). In other words, it worked fine for 250 times. Then suddenly generates an error. Once I restart Excel and start the macro beginning the count value when it stopped, it runs well again for some amount of iteration. Is there any reason for that? I have included the code below, hoping it doesn't take too much space. thanks Public Const start_section As Integer = 101 Public Const end_section As Integer = 411 Public Const Anal_Sheet As String = "ACI" Public Const My_Dir As String = "D:\Thesis\Analysis\Excel\Standards\" Dim i As Integer Sub ACI() Application.EnableEvents = False Application.ScreenUpdating = False For i = start_section To end_section 'do nothing for the same section If Sheets("DB").Cells(i + 6, 7) = Sheets("DB").Cells (i + 5, 7) And _ Sheets("DB").Cells(i + 6, 15) = Sheets("DB").Cells (i + 5, 15) Then ' do nothing Else 'do analysis if different section Sheets(Anal_Sheet).Activate Sheets(Anal_Sheet).Range("B5").Value = i 'change section Call GoalSeek 'find M, @P=0 Call Copy_and_Sort 'copy temp data (under chart) to the place and sort 'generate a new Sheet and copy data Sheets("temp_form").Copy After:=Sheets (Sheets.Count) Set new_sheet = Sheets(Sheets.Count) new_sheet.Name = i new_sheet.Range("b9:C9", Range("b9:c9").End (xlDown)).ClearContents With new_sheet .Range("A1") = i .Range("B1") = Sheets("DB").Range ("D1").Offset(i + 6, 0) .Range("B4:D4") = Sheets(Anal_Sheet).Range ("C5:E5").Value ' b, t, KL .Range("E4") = Sheets(Anal_Sheet).Range ("H5").Value ' Fy .Range("F4") = Sheets(Anal_Sheet).Range ("M5").Value ' f'c .Range("G4") = Sheets(Anal_Sheet).Range ("B17").Value ' KL/r .Range("H4") = Sheets(Anal_Sheet).Range ("D17").Value ' short column .Range("D9:F21") = Sheets(Anal_Sheet).Range ("D23:F35").Value ' copy P, M from ACI to here End With End If Sheets(Sheets.Count).Range("B65535").End (xlUp).Offset(1, 0) = Sheets("DB").Cells(i + 6, 16).Value Sheets(Sheets.Count).Range("C65535").End (xlUp).Offset(1, 0) = Sheets("DB").Cells(i + 6, 19).Value If Sheets("DB").Cells(i + 6, 4) = Sheets ("DB").Cells(i + 7, 4) Then ' do nothing Else: Call MoveSheets End If Workbooks("us standards").Save Next i Application.ScreenUpdating = False Application.EnableEvents = True End Sub Sub GoalSeek() 'goal seek Sheets(Anal_Sheet).Range("AO36").GoalSeek Goal:=0, ChangingCell:=Range("X12") 'copy the goal seek result Sheets(Anal_Sheet).Range("N30") = Sheets (Anal_Sheet).Range("x12").Value Sheets(Anal_Sheet).Range("X12") = 8 End Sub Sub Copy_and_Sort() 'copy temp data (under the chart) to the correct place Sheets(Anal_Sheet).Range("B24:E34") = Sheets(Anal_Sheet).Range("N20:Q30").Value Sheets(Anal_Sheet).Range("B24:E34").Sort Key1:=Sheets(Anal_Sheet).Range("B24"), Order1:=xlAscending End Sub Sub MoveSheets() Dim shtArray() As Integer Dim shts As Integer Sheets(Anal_Sheet).Activate For shts = Sheets("temp_form").Index + 1 To Sheets.Count ReDim Preserve shtArray(Sheets("temp_form").Index + 1 To shts) shtArray(shts) = shts Next shts Sheets(shtArray).Move ActiveWorkbook.SaveAs Filename:=My_Dir & i ActiveWorkbook.Close End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error message I have never seen before | Excel Discussion (Misc queries) | |||
VBA Error Message "Compile Error...." | Excel Discussion (Misc queries) | |||
Error Message | Excel Discussion (Misc queries) | |||
changing the message in an error message | Excel Worksheet Functions | |||
How do I get rid of "Compile error in hidden module" error message | Excel Discussion (Misc queries) |