There are two things you can try

1) comment out the One Error statements while debugging. It make it easier
to find problems

2) There is a menu option in VBA to stop on all errors

Tools - OPtions - General - Error Trapping

Change to Stop On All Errors.


3) In large prgrams you probably want to add break point in th VBA to hepl
narrow down where the error is occuring. Using F8 could take a long time

Select a line of code in VBA then press F9. Run program. if you get to
break point then set another break point futher down in the code. if yo
don't get to the break point set the break point earlier in the code. Once
yo get to a break point your can step using F8 or continue using F5.

"WhytheQ" wrote:

Hello All,

I've posted about this previously but still haven't solved the problem
- really banging my head against a brock wall!

I've got a large program which runs in a second instance of Excel. It
doesn't bug out but just stops part way though - when I open the
second instance there will be a workbook open in design mode.

How do I go about finding the problem?

Any help much appreciated
Jason


'======================================
here's the code (....there's quite a bit!)..........


Option Explicit

Private Const mySummaryStem As String = "R:\Statistics\Reporting\Daily
Summary\Daily summary 0.4\"
Private Const myStorageFileStore As String = "R:\Statistics\Reporting
\Daily Summary\Daily summary 0.4\Data Storage Sheets 0.4\"
Private Const mySummaryFilePath As String = "R:\Statistics\Reporting
\DailySummary\Daily summary 0.4\Daily Casino Summary 0.4.xlsm"
Private Const myStorageTemplatePath As String = "R:\Statistics
\Reporting\Daily Summary\Daily summary 0.4\Daily Storage Template
0.4.xlsx"
Private Const myFeedFilePath As String = "R:\Statistics\Reporting
\Daily Summary\Daily summary 0.4\Daily Feed 0.4.xlsm"

Private myFeedBook As Workbook

Private rSource As Range
Private rDest As Range

Private AlreadyUpdated As Boolean
Private blUpdateAll As Boolean
Private blUpdateFormatting As Boolean

Private blSaveStorageSheet As Boolean

Private oPivCatRange As Range

'Private oItem
'Private oItem As String
Private myItem As String
Private myStorageName As String
Private EndCell As Integer
Private j As Integer


Private myLastRow
Private myStorageBook As Workbook
Private mySheet As Worksheet

Private mySummaryBook As Workbook
Private i As Integer


Public Sub UpdateFeedWorkbook()

Application.ScreenUpdating = False
Set myFeedBook = Workbooks.Open(myFeedFilePath, , False, , , ,
True)

With myFeedBook
.Sheets("Daily_QueryTable").ListObjects
(1).QueryTable.Refresh BackgroundQuery:=False
.Sheets("Pivot").PivotTables
("PivotTable3").PivotCache.Refresh
.Sheets("Pivot2").PivotTables
("PivotTable1").PivotCache.Refresh

Set rSource = .Sheets("Pivot2").Range("C5:C"
& .Sheets("Pivot2").Cells(.Sheets("Pivot2").Rows.Cou nt, 3).End
(xlUp).Row)
Set rDest = ThisWorkbook.Sheets("Static_Data").Range
("S6")
With rSource
Set rDest = rDest.Resize
(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value

End With

Set myFeedBook = Nothing
Application.ScreenUpdating = True

End Sub
Public Sub UpdateStorageBooksAndSummary()



blUpdateAll = False
Application.ScreenUpdating = True
If MsgBox("Do you wish to update all storage sheets irrespective
as to whether they have already been saved today?", vbYesNo +
vbDefaultButton2, "Overwrite Existing Files") = vbYes Then
Application.ScreenUpdating = False
blUpdateAll = True
End If
Application.ScreenUpdating = False

blUpdateFormatting = False
Application.ScreenUpdating = True
If MsgBox("Do you wish to update sheet formatting?", vbYesNo +
vbDefaultButton2, "Update formatting") = vbYes Then
Application.ScreenUpdating = False
blUpdateFormatting = True
End If
Application.ScreenUpdating = False




'========open the summary file
'open summary file
If IsFileOpen(ExtractFileName(mySummaryFilePath)) = False Then
Workbooks.Open mySummaryFilePath, , False, , , , True
End If
Set mySummaryBook = Workbooks(ExtractFileName(mySummaryFilePath))
'========

'clear out the data sheets that were previously collated from
the storage sheets
With mySummaryBook
.Sheets("Data_Measures").Range("A2:AZ10000").Clear Contents
.Sheets("Data_MaxMin").Range("A2:AZ10000").ClearCo ntents
.Sheets("Data_Graphs").Range("A4:G10000").ClearCon tents
.Sheets("Data_Graphs").Range("J4:M10000").ClearCon tents
.Sheets("Data_Graphs").Range("P4:R10000").ClearCon tents
End With
'========



'========open the feed file
'open feed file
If IsFileOpen(ExtractFileName(myFeedFilePath)) = False Then
Workbooks.Open myFeedFilePath, , False, , , , True
End If
Set myFeedBook = Workbooks(ExtractFileName(myFeedFilePath))
'========



'========open all storage sheets
'look at the category names in the pivot on the Control sheet
'With ThisWorkbook.Sheets("Static_Data")
' Set oPivCatRange = .Range("StorageSheetsToUpdate")
'End With

i = 1

EndCell = ThisWorkbook.Sheets("Static_Data").Range("C100").E nd
(xlUp).Row

'loop through the category names, which correspond to the
storage book names
'For Each oItem In oPivCatRange.Cells
For j = 6 To EndCell

myItem = ThisWorkbook.Sheets("Static_Data").Cells(j,
3).Value
myStorageName = myItem & ".xlsx"


If myItem < "" Then

'check if NOT saved today;
AlreadyUpdated = False
If FileDateTime(myStorageFileStore &
myStorageName) Date And blUpdateAll = False Then
AlreadyUpdated = True
End If


'=======open each Storage book - always opens
file to move data to summary
Set myStorageBook = Workbooks.Open
(myStorageFileStore & myStorageName) ', , False, , , , True


'=======clear out old data if not already
updated
If AlreadyUpdated = True Then
Else
With myStorageBook.Sheets("Input")
.Range("C6:AZ500").ClearContents
.Range("D2").ClearContents
End With
End If
'=========================================



'=======copy data into Storage sheet
If AlreadyUpdated = True Then
Else
With myFeedBook.Sheets("Pivot")

Application.ScreenUpdating =
True '#########################NEW 21AUG09
.Range("E3").Value = myItem
Application.ScreenUpdating =
False '#########################NEW 21AUG09

myLastRow = .Cells(Rows.Count,
4).End(xlUp).Row

Set rSource = .Range("D7:D" &
myLastRow)
Set rDest = myStorageBook.Sheets
("Input").Range("C7")
With rSource
Set rDest = rDest.Resize
(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value

Set rSource = .Range("B6:B" &
myLastRow)
Set rDest = myStorageBook.Sheets
("Input").Range("D6")
With rSource
Set rDest = rDest.Resize
(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value

Set rSource = .Range("E6:AZ" &
myLastRow)
Set rDest = myStorageBook.Sheets
("Input").Range("E6")
With rSource
Set rDest = rDest.Resize
(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value

Set rSource =
Nothing '#########################NEW
19AUG09
Set rDest =
Nothing
'#########################NEW 19AUG09

End With
End If
'=========================================



'=======copy data out of Storage
sheet==========
With Workbooks(myStorageName).Sheets
("Summary")
.Activate

Set rSource = .Range("C5:BG" & .Range
("B46").Value + 4)
Set rDest = mySummaryBook.Sheets
("Data_Measures").Cells(Rows.Count, 4).End(xlUp)(2, 1)
With rSource
Set rDest = rDest.Resize
(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value

Set rSource =
Nothing '#########################NEW
19AUG09
Set rDest =
Nothing
'#########################NEW 19AUG09

End With
With mySummaryBook.Sheets("Data_Measures")
.Range("B" & .Cells(.Rows.Count, 2).End
(xlUp).Row + 1 & ":B" & .Cells(.Rows.Count, 4).End(xlUp).Row) =
Workbooks(myStorageName).Sheets("Summary").Range(" C2").Value
.Range("C" & .Cells(.Rows.Count, 3).End
(xlUp).Row + 1 & ":C" & .Cells(.Rows.Count, 4).End(xlUp).Row) = myItem
End With
'=======

'copy graph data out of Storage sheet
With Workbooks(myStorageName).Sheets("All
Operator")
.Activate
'#########################NEW 19AUG09

Application.ScreenUpdating =
True '#########################NEW 21AUG09

Set rSource = .Range("AH7:AL43")