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
* 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
* The copies are renamed repeatedly consistent with their Event
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
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
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
Step 1: Delete the old case outputs.
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Left(ws.Name, 2) = "E-" Then
Step 2: Create new outputs as they are created:
Sub SaveEventOutputs(thisEvent As Integer)
.Name = "E-" & Format(thisEvent) & "A"
Operation:=xlNone, SkipBlanks _
Application.CutCopyMode = False
'second sheet copy code deleted here