Code only working in debug mode.

[greg.begin]

I have this code here that works when i go through the debugger and then when i run the macro it doesn't work. I have removed some titles for sensitive reasons, but the structure is intact.

Sub Disseminate()
Dim RefName As String
Dim RefDate As String
Dim Names As Integer
Dim I As Integer
Dim rRows As Integer
Dim fName As String
Dim dDate As String
Dim tRows As Integer
Application.ScreenUpdating = False
rRows = WorksheetFunction.CountA(Sheets("etc").Range("a1:a500")) + 3
Names = Sheets("sheet2").Columns.CurrentRegion.Rows.Count - 1
For I = 1 To Names
RefName = Sheets("sheet2").Range("a" & I + 1).Value
RefDate = Range("f1").Value
ActiveSheet.Range("$A$3:$AE$" & rRows).AutoFilter Field:=1, Criteria1:=RefName
If Dir("Some Directory\" & RefName & ".xlsm") <> "" Then
Workbooks.Open Filename:= _
  "("Some Directory\" & RefName & ".xlsm", ReadOnly:=False
 
 
  Workbooks(RefName & ".xlsm").Activate
  On Error Resume Next
  Sheets(RefDate).Delete
 
'This is where the code seems to stop
 
 
  Worksheets.Add().Name = RefDate
 
  Sheets(RefDate).Select
Else
  ActiveWorkbook.SaveAs Filename:= _
  "("Some Directory\" & RefName & ".xlsm", FileFormat _
  :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
  Worksheets.Add().Name = RefDate
 
End If
Windows("Main Sheet.xlsm").Activate
  Range("A1").Select
  Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
  Selection.Copy
  Windows(RefName & ".xlsm").Activate
  Range("a1").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
  xlNone, SkipBlanks:=False, Transpose:=False
  Range("a1").PasteSpecial Paste:=xlPasteComments
Workbooks(RefName & ".xlsm").Worksheets(RefDate).Cells.EntireColumn.AutoFit
tRows = WorksheetFunction.CountA(Range("a3:a10000"))
 
  ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$3:$AF$" & tRows), , xlYes).Name = _
  "Table1"
  Rows("1:2").Delete Shift:=xlUp
 
  ActiveSheet.ListObjects("Table1").TableStyle = ""
ActiveWorkbook.Close savechanges:=True
 
Next I
ActiveSheet.Range("$A$3:$AE$" & rRows).AutoFilter Field:=1
dDate = Format([today()], "MM-DD-YYYY")
fName = "Main sheet "
fName = fName & dDate
ActiveWorkbook.SaveAs Filename:= _
  "("Some Directory\" & fName _
  , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
ActiveWorkbook.Close savechanges:=False
Application.ScreenUpdating = True
End Sub

Please help.

 

[SirJB7]

Hi, greg.begin!

A few questions:
a) When executing the macro with F8, does any line (specially that in which you say that it seems to stop) appear to delay more, i.e., next yellow shaded line appears with delay?
b) We're talking about a large workbook? How does it weight in Mb and how many time it demands for being saved?
c) Tried inserting a DoEvents instruction after each Worksheets.Add or after the If...EndIf structure?

Regards!

 

[NARAYANK991]

Hi Greg ,

Can it be because the code is trying to add a worksheet tab named RefDate immediately after it deletes a worksheet tab of the same name ?

Sheets(RefDate).Delete

'This is where the code seems to stop

Worksheets.Add().Name = RefDate

If this is a possible reason , just introduce a Application.Wait as in :

Application.Wait(Now + TimeValue("0:00:05"))

between the above two statements

Narayan

 

[greg.begin]

Hi, greg.begin!

A few questions:
a) When executing the macro with F8, does any line (specially that in which you say that it seems to stop) appear to delay more, i.e., next yellow shaded line appears with delay?
b) We're talking about a large workbook? How does it weight in Mb and how many time it demands for being saved?
c) Tried inserting a DoEvents instruction after each Worksheets.Add or after the If...EndIf structure?

Regards!

a) There is no delay
b)118 kb is the size, the code does require the file being opened and pasted to to be saved, currently loops 9 times.
c) inserted DoEvents in both locations, one at a time and same thing happens. Stops in same location and ends the code, not debugg error, just stops.

 

[greg.begin]

Hi Greg ,

Can it be because the code is trying to add a worksheet tab named RefDate immediately after it deletes a worksheet tab of the same name ?

Sheets(RefDate).Delete

'This is where the code seems to stop

Worksheets.Add().Name = RefDate

If this is a possible reason , just introduce a Application.Wait as in :

Application.Wait(Now + TimeValue("0:00:05"))

between the above two statements

Narayan

I added the wait code between the two and getting the same error in same place; no debug code or error code, just stops the vba and the VBA application is not opened either, just literally nothing.

 

[NARAYANK991]

Hi Greg ,

Can you comment out the following two lines , and see whether any informative error message is displayed ?

1. Application.ScreenUpdating = False
2. On Error Resume Next

Narayan

 

[SirJB7]

Hi, greg.begin!
You know the routine yet... upload a sample file and so on. Would it be possible?
Regards!

 

[greg.begin]

Hi, greg.begin!
You know the routine yet... upload a sample file and so on. Would it be possible?
Regards!

Yes, i know the routine; however, i am absolutely not allow to upload or download anything from the internet with out IT security analyzing and approving. so that is not an option for me.

 

[greg.begin]

Hi Greg ,

Can you comment out the following two lines , and see whether any informative error message is displayed ?

1. Application.ScreenUpdating = False
2. On Error Resume Next

Narayan

Hi Greg ,

Can you comment out the following two lines , and see whether any informative error message is displayed ?

1. Application.ScreenUpdating = False
2. On Error Resume Next

Narayan

I commented them out and still, exact problem persists.

 

[NARAYANK991]

Hi Greg ,

In case you want to send me the file by email , send it to me at narayank1026 [at] gmail [dot] com.

Narayan