error message

[Young-Hwan Choi]

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("B44") = 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

 

[Nicke]

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\"