CODEWITHSUNDEEP
vbaexcelerror-handling
Sean Bailey
Sean Bailey

Reputation: 375

VBA code restarting halfway through without error message

I have the below code it seems to work fine however halfway through it, it stops and restarts when I am debugging the code.

When it restarts is does not show any error message. Does anyone know why my code is doing this?

Sub ExportPICs()

If FileIsOpen("U:\FBS\PROJECTS_TEAM\MI\Sean's Projects\PICS and Benefits\Pics & Benefits upload file.xlsm") Then
    MsgBox "That file's open, or doesn't exist - do something else."
    Exit Sub
    End If

Sheets("Post Implementation Costs").Visible = True
Sheets("Post Implementation Costs").Activate
Sheets("Post Implementation Costs").Unprotect Password:="Projects123"

Dim Tracker As String
Tracker = ThisWorkbook.Name

Dim wkbk As Excel.Workbook
Set wkbk = Workbooks.Open(Filename:= _
"U:\FBS\PROJECTS_TEAM\MI\Sean's Projects\PICS and Benefits\Pics & Benefits upload file.xlsm") '***********CODE RESTARTS HERE WITHOUT ERROR MESSAGE

Dim pics As String
pics = Dir("U:\FBS\PROJECTS_TEAM\MI\Sean's Projects\PICS and Benefits\Pics & Benefits upload file.xlsm")


Workbooks(Tracker).Activate
Sheets("Post Implementation Costs").Activate

Dim rng As Range
Dim CountTrue As Long
Set rng = Range("D16:D100")
CountTrue = Application.WorksheetFunction.CountIf(rng, "PIC")

If CountTrue > 0 Then
Rows("19:" & (CountTrue + 18)).EntireRow.Copy
End If

End Sub

Upvotes: 1

Views: 1208

Answers (2)

Moosli
Moosli

Reputation: 3285

If you have any code in the Workbook Open Event in "Pics & Benefits upload file.xlsm" that would do the Loop.

Try Application.EnableEvents = False

before

Dim wkbk As Excel.Workbook
Set wkbk = Workbooks.Open(Filename:= _
"U:\FBS\PROJECTS_TEAM\MI\Sean's Projects\PICS and Benefits\Pics & Benefits upload file.xlsm")

And after that you can activate it again via Application.EnableEvents = True

This code stops Excel event handlers from being called. Setting it to false is usually done because the effect of the event handler is undesirable or to prevent an infinite loop.

Upvotes: 2

Paul Ogilvie
Paul Ogilvie

Reputation: 25286

I have discovered too that with some statements the Step command doesn't step but lets all the following code run. To avoid this, set a breakpoint at the line following where the step gets confused.

Upvotes: 0

Related Questions