I have a strange problem. I'm developing an optimization model in
Excel using an external solver.

* A model run optimizes many separate Events.
* Each Event has a 3 number designation
* The same Event numbers are re-optimized over many parameter changes
(cases)
* Each optimized case result is saved to two output worksheets by
making copies off Model and Output sheets that talk to the solver
* At the start of each optimization, the existing Event output sheets
are deleted
* The copies are renamed repeatedly consistent with their Event
numbers.

The Problem: The sheet copy routine randomly fails with the message:

Runtime Error '1004'
Copy method of Worksheet Class Failed

When I say randomly, I mean randomly as the model walks through the
Event list. If I'm processing 6 Events. It may execute the Event
list properly 3 times and then fail on the 4th. Sometimes it's on the
second, other times on the 5th. You get my drift. Once it fails, it
stays failed. It's like it thinks the deleted sheets are still there
so fails on the rename. But that's a guess.

Recompiling the Project does not work. However, closing and opening
the model workbook does work! And then it's back to random failure
mode. Hmmm...?

Sorry for being so dense. Below are the two simple delete and copy
subs. If any of you geniuses out there can catch this gremlin, I
would surely appreciate it. Below is the code for the delete and copy
subs.

This thing has me beat. I'm going to drink semi-heavily tonight and
hope a miracle solution appears on this user group doorstep in the
morning..

Thanks Much,

SteveM

Step 1: Delete the old case outputs.

Sub DeleteEvents()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If Left(ws.Name, 2) = "E-" Then
ws.Delete
End If
Next

End Sub

Step 2: Create new outputs as they are created:

Sub SaveEventOutputs(thisEvent As Integer)

Worksheets("Model").Copy After:=Worksheets(Worksheets.Count)
With ActiveSheet
.Name = "E-" & Format(thisEvent) & "A"
.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
End With

'second sheet copy code deleted here

End Sub

Repost to surface above the MI5 Posts - Still looking for some ideas.

Thanks,

Steve M

On Dec 15, 6:29 pm, SteveM wrote:
I have a strange problem. I'm developing an optimization model in
Excel using an external solver.

* A model run optimizes many separate Events.
* Each Event has a 3 number designation
* The same Event numbers are re-optimized over many parameter changes
(cases)
* Each optimized case result is saved to two output worksheets by
making copies off Model and Output sheets that talk to the solver
* At the start of each optimization, the existing Event output sheets
are deleted
* The copies are renamed repeatedly consistent with their Event
numbers.

The Problem: The sheet copy routine randomly fails with the message:

Runtime Error '1004'
Copy method of Worksheet Class Failed

When I say randomly, I mean randomly as the model walks through the
Event list. If I'm processing 6 Events. It may execute the Event
list properly 3 times and then fail on the 4th. Sometimes it's on the
second, other times on the 5th. You get my drift. Once it fails, it
stays failed. It's like it thinks the deleted sheets are still there
so fails on the rename. But that's a guess.

Recompiling the Project does not work. However, closing and opening
the model workbook does work! And then it's back to random failure
mode. Hmmm...?

Sorry for being so dense. Below are the two simple delete and copy
subs. If any of you geniuses out there can catch this gremlin, I
would surely appreciate it. Below is the code for the delete and copy
subs.

This thing has me beat. I'm going to drink semi-heavily tonight and
hope a miracle solution appears on this user group doorstep in the
morning..

Thanks Much,

SteveM

Step 1: Delete the old case outputs.

Sub DeleteEvents()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If Left(ws.Name, 2) = "E-" Then
ws.Delete
End If
Next

End Sub

Step 2: Create new outputs as they are created:

Sub SaveEventOutputs(thisEvent As Integer)

Worksheets("Model").Copy After:=Worksheets(Worksheets.Count)
With ActiveSheet
.Name = "E-" & Format(thisEvent) & "A"
.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
End With

'second sheet copy code deleted here

End Sub

Forget it. It's an MS screwup.

http://support.microsoft.com/default...b;en-us;210684

The solutions stink. I can't close and re-open the workbook because
the unique optimization iterations are sequenced. And creating
templates without the data provides no value.

I'm sure you guys are anguishing over my troubles...

I guess the solution is to try to insert new sheets and copy and paste
the data.

SteveM


On Dec 16, 12:31 pm, SteveM wrote:
Repost to surface above the MI5 Posts - Still looking for some ideas.

Thanks,

Steve M

On Dec 15, 6:29 pm, SteveM wrote:

I have a strange problem. I'm developing an optimization model in
Excel using an external solver.

* A model run optimizes many separate Events.
* Each Event has a 3 number designation
* The same Event numbers are re-optimized over many parameter changes
(cases)
* Each optimized case result is saved to two output worksheets by
making copies off Model and Output sheets that talk to the solver
* At the start of each optimization, the existing Event output sheets
are deleted
* The copies are renamed repeatedly consistent with their Event
numbers.

The Problem: The sheet copy routine randomly fails with the message:

Runtime Error '1004'
Copy method of Worksheet Class Failed

When I say randomly, I mean randomly as the model walks through the
Event list. If I'm processing 6 Events. It may execute the Event
list properly 3 times and then fail on the 4th. Sometimes it's on the
second, other times on the 5th. You get my drift. Once it fails, it
stays failed. It's like it thinks the deleted sheets are still there
so fails on the rename. But that's a guess.

Recompiling the Project does not work. However, closing and opening
the model workbook does work! And then it's back to random failure
mode. Hmmm...?

Sorry for being so dense. Below are the two simple delete and copy
subs. If any of you geniuses out there can catch this gremlin, I
would surely appreciate it. Below is the code for the delete and copy
subs.

This thing has me beat. I'm going to drink semi-heavily tonight and
hope a miracle solution appears on this user group doorstep in the
morning..

Thanks Much,

SteveM

Step 1: Delete the old case outputs.

Sub DeleteEvents()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If Left(ws.Name, 2) = "E-" Then
ws.Delete
End If
Next

End Sub

Step 2: Create new outputs as they are created:

Sub SaveEventOutputs(thisEvent As Integer)

Worksheets("Model").Copy After:=Worksheets(Worksheets.Count)
With ActiveSheet
.Name = "E-" & Format(thisEvent) & "A"
.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
End With

'second sheet copy code deleted here

End Sub