HOW DO I TROUBLESHOOT A CODE INTERUPTION?

[Mr_Phil]

This code works sometimes and others I get the "code interuption" error. I can continue and it works. And it seems to pick from two different places in the code to stop at on random basis. Since I can discern a pattern I kinda don't know what to do to troubleshoot it. Any advice?

Sub SaveAsCopy()
  'Saves copy of workbook to filename as value of Q1
  Dim PathName, FileName As String
  FileName = Range("Q1").Value & ".xlsm"
  If Len(FileName) > 0 Then
    PathName = ThisWorkbook.Path
    If Dir(PathName, vbDirectory) = "" Then MkDir PathName
    ThisWorkbook.SaveAs PathName & "\" & FileName
  Else
    Range("q1").Select
     MsgBox "File name in P1 is not found", vbExclamation, "Not saved": Exit Sub
  End If
  Range("h11:h500").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    'ActiveWindow.ScrollColumn = 2
    'ActiveWindow.ScrollColumn = 1
    Application.SendKeys ("{ESC}")
    Range("e2").Select '<<<<<<<<<<<this is the most common line highlighted in debugger
    Rows("2:7").Select
    Range("E2").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False ' <<<<<<<<<<<<<<<<<This is the next most common line.
    Range("F8").Select
        Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Sheets("Inventory Calculations").Select
    Columns("A:s").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[jasonb75]

Not something I've ever had cause to do so I could be wrong, but I'm pretty sure that the purpose of the line, Application.SendKeys ("{ESC}") is to interrupt the code.

 

[Mr_Phil]

Not something I've ever had cause to do so I could be wrong, but I'm pretty sure that the purpose of the line, Application.SendKeys ("{ESC}") is to interrupt the code.

Heh. I have no idea why that was put there. I am a learn by google sort of guy so would not be surprized if it was part of a sample script I found online. I will comment it out and see. Thanks for the pointer.

 

[Mr_Phil]

Not something I've ever had cause to do so I could be wrong, but I'm pretty sure that the purpose of the line, Application.SendKeys ("{ESC}") is to interrupt the code.

I commented it out and ran it a half dozen times and it worked. Thank you again for your help.

 

[jasonb75]

To be fair, I'm looking at much bigger issues in your code than the line that you asked about.

Looking at the way you're doing copy and paste, I would expect some serious file bloat (this is when files are much bigger than they should be because of copying empty cells).

Roughly how many rows and columns of data are there in the sheet that you're working with?

 

[Mr_Phil]

To be fair, I'm looking at much bigger issues in your code than the line that you asked about.

Looking at the way you're doing copy and paste, I would expect some serious file bloat (this is when files are much bigger than they should be because of copying empty cells).

Roughly how many rows and columns of data are there in the sheet that you're working with?

It will average about 26 columns by 500 to 1000 rows. It really depends on how busy we are the week and month prior as this aggregates various location orders. I will take any and all advice, links, tutorials etc and seriously be a happy guy. Thanks.

 

[Michael M]

This may be a little quicker AND doesn't limit your rnage to 500 rows

Sub SaveAsCopy()
'Saves copy of workbook to filename as value of Q1
Dim PathName, FileName As String, lr As Long, lr2 As Long
lr = Cells(Rows.Count, "H").End(xlUp).Row
FileName = Range("Q1").Value & ".xlsm"
If Len(FileName) > 0 Then
    PathName = ThisWorkbook.Path
    If Dir(PathName, vbDirectory) = "" Then MkDir PathName
    ThisWorkbook.SaveAs PathName & "\" & FileName
    Else
    MsgBox "File name in P1 is not found", vbExclamation, "Not saved": Exit Sub
End If
With Range("H11:H" & lr)
    .Value = .Value
End With
With Range("E2", "F8")
    .Value = .Value
End With
Sheets("Inventory Calculations").Select
lr2 = Cells(Rows.Count, "A").End(xlUp).Row
With Range("A1:S" & lr2)
    .Value = .Value
End With
End Sub

 

[Mr_Phil]

This may be a little quicker AND doesn't limit your rnage to 500 rows

Sub SaveAsCopy()
'Saves copy of workbook to filename as value of Q1
Dim PathName, FileName As String, lr As Long, lr2 As Long
lr = Cells(Rows.Count, "H").End(xlUp).Row
FileName = Range("Q1").Value & ".xlsm"
If Len(FileName) > 0 Then
    PathName = ThisWorkbook.Path
    If Dir(PathName, vbDirectory) = "" Then MkDir PathName
    ThisWorkbook.SaveAs PathName & "\" & FileName
    Else
    MsgBox "File name in P1 is not found", vbExclamation, "Not saved": Exit Sub
End If
With Range("H11:H" & lr)
    .Value = .Value
End With
With Range("E2", "F8")
    .Value = .Value
End With
Sheets("Inventory Calculations").Select
lr2 = Cells(Rows.Count, "A").End(xlUp).Row
With Range("A1:S" & lr2)
    .Value = .Value
End With
End Sub

Thank you very much. I ran it and it worked great. Noticeably quicker than what I did with the recorder and google. Thanks again for your help in this.