View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
[email protected] teilenvk@hotmail.com is offline
external usenet poster
 
Posts: 5
Default Macros skip steps and/or stops in the middle of code

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