I'm running Excel 2003 SP2 on a Win 2000 Pro system and after nearly a
month of development my code occassionally stops working!

I am using a macros to copy a hidden sheet with graphs based on a pivot
table, rename the copy, and change the row field of the copied pivot
table (which changes the graphs). I use 19 of these macros to graph 19
different subjects (or row fields) each on their own newly created
sheet. If the macro is rerun it asks the user if they want to keep the
old sheet or delete and rerun. I don't beleive I've ever had a problem
running the 19 macros individually (save one, which inconsistantly
changes graphs from plotting by columns to rows).

I also have a macro which runs all 19 macros at once. The first time I
run that macro all 19 work fine. However if I rerun the "all" macro
some of the 19 work and others don't. Same problem if I run any of
the 19 individually after running the "all" macro. Which of the 19 to
have problems is not conistant nor are the problems. When I step into
the macros they either work fine or stop half way through (the macro is
no longer playing an a number of stops where never hit).

I have disabled "Auto Scale" fonts on the graph area formates. I have
also try to prevent file fragmentation by rebuilding the file without
macros and copying and pasting my code into the new file.

I've tried everything I can think of, spent days search the web, and am
now considering a month of work wasted. What is going on with my
file/code?

Thanks for any help you can give!

Teilen

Teilen,
Do you have Error handling in your code?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




wrote in message
I'm running Excel 2003 SP2 on a Win 2000 Pro system and after nearly a
month of development my code occassionally stops working!

I am using a macros to copy a hidden sheet with graphs based on a pivot
table, rename the copy, and change the row field of the copied pivot
table (which changes the graphs). I use 19 of these macros to graph 19
different subjects (or row fields) each on their own newly created
sheet. If the macro is rerun it asks the user if they want to keep the
old sheet or delete and rerun. I don't beleive I've ever had a problem
running the 19 macros individually (save one, which inconsistantly
changes graphs from plotting by columns to rows).

I also have a macro which runs all 19 macros at once. The first time I
run that macro all 19 work fine. However if I rerun the "all" macro
some of the 19 work and others don't. Same problem if I run any of
the 19 individually after running the "all" macro. Which of the 19 to
have problems is not conistant nor are the problems. When I step into
the macros they either work fine or stop half way through (the macro is
no longer playing an a number of stops where never hit).

I have disabled "Auto Scale" fonts on the graph area formates. I have
also try to prevent file fragmentation by rebuilding the file without
macros and copying and pasting my code into the new file.

I've tried everything I can think of, spent days search the web, and am
now considering a month of work wasted. What is going on with my
file/code?
Thanks for any help you can give!
Teilen

Do you mean "On Error Resume Next"? No here's the code
----------------------------------------------------------------------------------------------------------------

Sub RunAllADS()

ADSTriangleCheck
LossLimitCredibility
LFMStates
CauseGroup
Cause
PartofBody
Location
LocationGroup
Tenure
Month
Day
Time
Age
ReportLagGroup
LossLayer
ManualClassCode
StateAccident
StateBureau
StateJurisdiction
End Sub

----------------------------------------------------------------------------------------------------------------

Sub Cause()

'Stores analyses variables for the RefAnalyses and RefAnalysesCompleted
macros
Sheets("Definitions").Range("AE2") = "Cause"
Sheets("Definitions").Range("AE3") = "Injury Cause Definition"
Sheets("Definitions").Range("AE4") = "Cause Analysis"
Sheets("Definitions").Range("AE5") = "Cause"
X = Sheets("Definitions").Range("AE2")

Application.ScreenUpdating = False
'Checks to see if the macro has already been run by the existance of a
sheet named...
On Error Resume Next
Set wSheet = Worksheets(X)
If wSheet Is Nothing Then
'If no sheet exists then copy the Graphs sheet, rename the copy, make
visible, and select for the next step
RefAnalyses
Else
RefAnalysesCompleted
End If
Application.ScreenUpdating = True
End Sub

----------------------------------------------------------------------------------------------------------------

Sub RefAnalyses() 'used in CauseGroup, Part of Body, etc.

'Retrieves variables stored by the analyses selected
X = Sheets("Definitions").Range("AE2")
Y = Sheets("Definitions").Range("AE3")

'Copy the Graphs sheet, rename the copy, make visible, and select for
the next step
Sheets("Graphs").Copy After:=Sheets(Sheets.Count)
With Sheets("Graphs (2)")
.Name = X
.Visible = True
.Select
End With
'Update the pivot table in the new sheet to have the desired row field
which then populates all the tables and graphs
With ActiveSheet.PivotTables("PivotTable1").PivotFields (Y)
.Orientation = xlRowField
.AutoSort xlDescending, "Sum of Selected Loss"
End With
ActiveWorkbook.ShowPivotTableFieldList = False
'Updates the graphs to the correct number of years and topics
UpdateGraphs
Range("P27").Select
End Sub

----------------------------------------------------------------------------------------------------------------

Sub RefAnalysesCompleted() 'used in CauseGroup, Part of Body, etc.

X = Sheets("Definitions").Range("AE2")
Z = Sheets("Definitions").Range("AE4")
M = Sheets("Definitions").Range("AE5")

'If the sheet already exists then ask user if rerun. Default is set to
keep existing sheet for safety
YN = MsgBox(Z & " already completed. Keep existing analysis?",
vbYesNo)
If YN = vbNo Then
'If don't keep existing then delete sheet and rerun original analysis
Application.DisplayAlerts = False
Sheets(X).Delete
Application.DisplayAlerts = True
Run (M)
Else
'If do keep existing then notify user no action was taken
MsgBox "You Cancelled"
End If
End Sub

On Error Resume Next ignores errors.
At the least, error handling can tell you what error occurred,
although Excel can sometimes just "quit" even when using error handling.

For starters use something like this to identify the error...

Sub RunAllADS()
On Error GoTo Err_Handler
'your code
Exit Sub
Err_Handler:
MsgBox Err.Num & vbCR & Err.Description
End Sub
'-------------------------------------

and maybe there is something in these...
http://support.microsoft.com/kb/210684/en-us
"Copying worksheet programmatically causes run-time error 1004 in Excel"
http://support.microsoft.com/kb/903240/en-us
Description of the Excel 2003 post-Service Pack 2 hotfix package: November 7, 2005
http://support.microsoft.com/kb/177634/en-us
XL97: Copy Method of Sheets Object Causes Invalid Page Fault
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html




wrote in message
Do you mean "On Error Resume Next"? No here's the code
---------------------------------------------------------------------------
Sub RunAllADS()

ADSTriangleCheck
LossLimitCredibility
LFMStates
CauseGroup
Cause
PartofBody
Location
LocationGroup
Tenure
Month
Day
Time
Age
ReportLagGroup
LossLayer
ManualClassCode
StateAccident
StateBureau
StateJurisdiction
End Sub
-snip-

Nothing came up with the Error Handling added to the code. However
after multiple testings (after each of which I closed without saving),
open the file gave me this message:

"Damage to the file was so extensive that repairs were not possible.
Excel attempted to recover your formulas and values, but some datamay
have been lost or corrupted."

What could my macros be doing to damage the file? Is there a maximum
amount of macros that can be run before a file becomes corrupt?

Thanks!
-Teilen

It is usually the code that the code writer writes that creates the problem.
However, Excel can be finicky and turn corrupt on the best of code writers.
Your basic blocking and tackling should come first...

Use Option Explicit as the top of each module.
Declare all variables and don't use Variants unless necessary.
Use error handling and avoid the use of on error resume next.
Keep your code modules under 64 kb. You can export a module to
a folder and check the size there. If you have 5,000 lines in a module
you are probably in trouble.
Keeping functions in one module and subs in another is one way to organize.
Sub divide those as appropriate.
Retain daily copies (or at more frequent intervals) of your code.
If you run into a problem it is easy then to go back to a non-problem version.
Make sure each procedure runs before starting another.
(with the on error resume statements commented out)
Develop your code in the oldest version of Excel expected to be used.
Test your code on all Excel versions that will be using it.
----
Jim Cone
San Francisco, USA


wrote in message
Nothing came up with the Error Handling added to the code. However
after multiple testings (after each of which I closed without saving),
open the file gave me this message:

"Damage to the file was so extensive that repairs were not possible.
Excel attempted to recover your formulas and values, but some datamay
have been lost or corrupted."

What could my macros be doing to damage the file? Is there a maximum
amount of macros that can be run before a file becomes corrupt?

Thanks!
-Teilen