Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 16th 07, 12:29 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 133
Default Need Help: Strange, Strange Behavior ( Not Me. My Model! )

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

  #2   Report Post  
Old December 16th 07, 06:31 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 133
Default Need Help: Strange, Strange Behavior ( Not Me. My Model! )

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


  #3   Report Post  
Old December 16th 07, 07:02 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 133
Default Need Help: Strange, Strange Behavior ( Not Me. My Model! )

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Strange behavior Patrick Simonds Excel Programming 2 November 25th 06 07:26 PM
Strange behavior Ritchie Excel Discussion (Misc queries) 2 September 26th 06 02:21 AM
VBA - Strange behavior MVM Excel Programming 0 June 12th 06 07:44 PM
Strange TAB behavior m davidson Excel Programming 4 May 28th 06 06:09 PM
Strange behavior. Wiley Coyote Excel Discussion (Misc queries) 7 October 18th 05 04:35 PM


All times are GMT +1. The time now is 09:48 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017