error while running macro, but not if stepping through lines.

[Brian S]

Hello,

If I run my macro code it errors at a specific spot each time, but if I
break before and step through the code and resume it works. The error comes
affter the delete "Sheet1".

Any advice? Here are the specifc lines if it helps:
h = Sheets.Count
Dim wbA As Workbook
Dim wbNew As Workbook
With Application
SheetsInWb = .SheetsInNewWorkbook
.SheetsInNewWorkbook = 1
.DisplayAlerts = False
.ScreenUpdating = False
End With
Set wbA = ThisWorkbook
Set wbNew = Workbooks.Add
With wbNew
.SaveAs Filename:="C:\Documents and Settings\ty756c\My
Documents\contract project\" & programname & ".xls"
End With
hh = h - 5
hhh = 1
For copysheet = 1 To hh
wbA.Sheets(5 + hhh).Copy wbNew.Sheets(hhh)
hhh = hhh + 1
Next copysheet
wbNew.Sheets("Sheet1").Delete
ActiveWorkbook.BreakLink Name:= _
"C:\Documents and Settings\ty756c\Desktop\nbfgraph macro.xls",
Type:= _
xlExcelLinks
ActiveWorkbook.Save
ActiveWorkbook.Close

 

[Barb Reinhardt]

I wonder if you'll find something here useful

Option Explicit

Sub BreakAWBLinks()
Dim myName As Name
Dim WorkbookLinks As Variant
Dim i As Long
Dim myWB As Excel.Workbook

Set myWB = ActiveWorkbook

WorkbookLinks = myWB.LinkSources(Type:=xlLinkTypeExcelLinks)
If Not IsArray(WorkbookLinks) Then
MsgBox "No Links to other workbooks in workbook"
End
End If

For i = LBound(WorkbookLinks) To UBound(WorkbookLinks)
myWB.BreakLink _
Name:=WorkbookLinks(i), _
Type:=xlLinkTypeExcelLinks
Next i



MsgBox ("Workbook links broken")

End Sub

 

[Martin Brown]

Hello,

If I run my macro code it errors at a specific spot each time, but if I
break before and step through the code and resume it works. The error comes
affter the delete "Sheet1".

I will hazard a guess that it is some kind of race condition where the
failing command doesn't find "Sheet1" or cannot break the link because
it is still in use by another process. Commenting out the DisplayAlerts
and ScreenUpdating lines might shed some more light. I have seen some
race conditions in chart axes on XL2007 but never on worksheets.

Obviously in single step or debug mode there is more time for whatever
needs to happen to complete. Adding a judicious delay after copying the
worksheets to the new workbook might be sufficient.

Regards,
Martin Brown

 

[Brian S]

Thanks for the help.

I will hazard a guess that it is some kind of race condition where the
failing command doesn't find "Sheet1" or cannot break the link because
it is still in use by another process. Commenting out the DisplayAlerts
and ScreenUpdating lines might shed some more light. I have seen some
race conditions in chart axes on XL2007 but never on worksheets.

Obviously in single step or debug mode there is more time for whatever
needs to happen to complete. Adding a judicious delay after copying the
worksheets to the new workbook might be sufficient.

Regards,
Martin Brown
.