Macro works in Debug mode but not in Runtime

[hajiali]

Sub Create()
    Dim i As Long
    Dim xNumber As Integer
    Dim xName As String
    Dim xActiveSheet As Worksheet
    On Error Resume Next
    Application.ScreenUpdating = False
    Set xActiveSheet = ActiveSheet
    xNumber = InputBox("ENTER NUMBER OF TIMES TO COPY THE CURRENT SHEET")
    For i = 1 To xNumber
        xName = ActiveSheet.Name
        xActiveSheet.Copy After:=ActiveWorkbook.Sheets(xName)
        With ActiveSheet
         .Name = i
         .Range("K1") = " "
         .Range("H1") = i
         .Range("J1") = Format(Date, "yyyy")
         .Range("d1") = "SEPT"
          Call scheduleformulachange
      End With
    Next
    xActiveSheet.Activate
    Application.ScreenUpdating = True
End Sub

Sub Copy_Unprotect_SCHEDULE()
    Call Create
    Sheets("SCHEDULE").Select
    ActiveWindow.SelectedSheets.Visible = False
    MsgBox "IMPORT IS COMPLETED", vbOKOnly
End Sub

I Recently got an issue with these codes only working when I run them in Debug mode put not in regular mode cannot figure out what's going on. When I run it in Runtime it seems as its never ending and freezing up excel. Help Please

 

[JLGWhiz]

Do you want to elaborate a little on what happens when you run the macro? Like what messages are you getting, or when does it go into limbo, etc. It seemed to work OK for me, except you did not post all the macros that it calls. Maybe it is the unposted macro that is causing the problem.

 

[hajiali]

Hello JLGWhiz it is strange no message appear. the mouse cursor continuously spins as its still process the macro. Excel completely goes blank like not even the the top tabs ex. File, Home, Page, layout, etc. disappear. I worked for me for a long time and all of a sudden its doing this. the other code it calls is as below: it worked for a long time even with this code

Sub scheduleformulachange()    [c4:d108].Replace "_AMBid", "_AMBid1", xlPart
    [j4:m108].Replace "_PMBid", "_PMBid1", xlPart
End Sub

 

[JLGWhiz]

OK, I cannot duplicate the problem, with test set up, so the problem must be in the content of your data. If it is not not malfunctioning when you step through the code, then it could be a timing problem with the macro calls. Look at how long it takes eack called macro to run and that could give a clue. If one part of the procedure advances before another has completed, it can cause Excel to shut down because the logic chain is broken. I cannot troubleshoot it by remote, so you will have to work with it there.

 

[hajiali]

This is really Strange because it used to work fine. here is some thing else i notice During Debug and regular the Imputbox comes up however while debug the mouse cursor keeps spinning until I click anywhere in excel than I get the Complete msgbox and everyworks. I even tried moving the Call scheduleformulachange() to the other macro Copy_Unprotect_SCHEDULE() Still same.

 

[mohadin]

Hi
have you tried to comment

Application.ScreenUpdating = False
Application.ScreenUpdating = True

 

[hajiali]

I tired it now and still same issue During Debug and regular the Imputbox comes up allows me to type a value, however while debug the mouse cursor keeps spinning until I click anywhere in excel than I get the Complete msgbox and everyworks. The debug only works when I click on anywhere on the sheet that is duplicating. Regular running the macro just put Excel in (not Responding) i have to Force close the software through Task Manager.

 

[JLGWhiz]

I tired it now and still same issue During Debug and regular the Imputbox comes up allows me to type a value, however while debug the mouse cursor keeps spinning until I click anywhere in excel than I get the Complete msgbox and everyworks. The debug only works when I click on anywhere on the sheet that is duplicating. Regular running the macro just put Excel in (not Responding) i have to Force close the software through Task Manager.

I am guessing, but the symptoms you describe indicate that you might have some event code that is being triggered and is interferring with the code you have posted here. Check your Sheet and Workbooks code modules for event code that might run as a result of running the posted code.

 

[hajiali]

All my Sheet and Workbooks are blank. For right now I just run the Macro through Debug mode until I can get a solution for it.