VBA: Excel has stopped working

[bruscus]

Hi All,

have been running a very simple macro for the last few months which very simple pulls in a file from another location and pastes some data. It then Fetches another file and at this point it seems to cause excel to crash and the "excel has stopped working" error.

This error has only been coming up in the last week prior to that it was running smoothly a version of the code as per below, any suggestions welcome

Sub SolicitorPMTS()




' FOLDER VARIABLES
 Dim ThisYear As String
 Dim ThisMonth As String
 Dim LastMonth As String
 Dim ThisLetter As String
 Dim ThisFolder As String
 Dim Folderyear As String
 Dim FolderPath As String
 Dim fso As New FileSystemObject
 Dim Path As String
 
 
 
Folderyear = year(DateAdd("m", -0, Date))
 
  'USED FOR LOOKUP TO PREVIOUS MONTH
 ThisYear = Format(DateAdd("m", -0, Date), "yy")
 ThisMonth = Format(DateAdd("m", -0, Date), "mmm")


 
 'THIS MONTH LETTERS
 Select Case ThisMonth
 Case Is = "Jan"
 ThisLetter = "a."
 Case Is = "Feb"
 ThisLetter = "b."
 Case Is = "Mar"
 ThisLetter = "c."
 Case Is = "Apr"
 ThisLetter = "d."
 Case Is = "May"
 ThisLetter = "e."
 Case Is = "Jun"
 ThisLetter = "f."
 Case Is = "Jul"
 ThisLetter = "g."
 Case Is = "Aug"
 ThisLetter = "h."
 Case Is = "Sep"
 ThisLetter = "i."
 Case Is = "Oct"
 ThisLetter = "j."
 Case Is = "Nov"
 ThisLetter = "k."
 Case Is = "Dec"
 ThisLetter = "l."
 End Select
 
   FolderPath = "File Location"
   
BSFolderPath = "File Location"
 


 
 wd = WorksheetFunction.WorkDay(Date, -1)
  
  
  
  
  FolderPath = "File Location"
  
  BSHalfFileName = "Bank Balances - "
  
    HalfFileName = "XXX - Daily Completions Funding - "


  ThisFolder = ThisLetter & ThisMonth & "-" & ThisYear


 Filename = HalfFileName & Format(Date, "dd") & ThisMonth & ThisYear & ".xls"
 
  bsfilename = BSHalfFileName & Format(Date, "dd") & ThisMonth & Folderyear & ".xlsx"
 
  prevfilename = HalfFileName & Format(wd, "dd") & ThisMonth & ThisYear & ".xls"
 


  
  
  
  Path = FolderPath & Folderyear & "\" & ThisFolder
  
  bspath = BSFolderPath & Folderyear & "\" & ThisFolder
  
  
  Workbooks("EXCELFILE.xlsb").Activate
    Worksheets("Solicitor Payments").Activate
        Range("A3").Select
        Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    
        Workbooks("EXCELFILE.xlsb").Activate
        Range("l3").Select
        Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    
  
'Import Today's Payments


Workbooks.Open Path & "\" & Filename


    Windows(Filename).Activate
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
Workbooks("EXCELFILE.xlsb").Activate
    Sheets("Solicitor Payments").Select
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        
        'Convert To Number
        
     Range("D3").Select
    Range(Selection, Selection.End(xlDown)).Select
With Selection
    .NumberFormat = "General"
    .Value = .Value
    End With








'Import Yesterday's Payments


Workbooks.Open Path & "\" & prevfilename


    Windows(prevfilename).Activate
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
Workbooks("TOM 3.xlsb").Activate
    Sheets("Solicitor Payments").Select
    Range("l3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False




        'Convert To Number
        
     Range("o3").Select
    Range(Selection, Selection.End(xlDown)).Select
With Selection
    .NumberFormat = "General"
    .Value = .Value
    End With
    
    Workbooks.Open bspath & "\" & bsfilename
    


    ActiveSheet.Range("a8").Select
     Range(Selection, Selection.End(xlDown)).Select
      Range(Selection, Selection.End(xlToRight)).Select
       Selection.Copy
        Workbooks("EXCELFILE.xlsb").Activate
         Worksheets("INPUT_Bank Statement").Activate
          Range("a6").Select
           ActiveSheet.Paste
            Worksheets("Daily Balances").Activate
            Range("zz3").Select
             Selection.End(xlToLeft).Select
              Range(Selection, Selection.End(xlDown)).Select
               Selection.Copy
                Selection.Offset(0, 1).Select
                 ActiveSheet.Paste
                Range("zz3").Select
                 Selection.End(xlToLeft).Select
                 Selection.Offset(0, -1).Select
                 Range(Selection, Selection.End(xlDown)).Select
                 Selection.Copy
                  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                 :=False, Transpose:=False
                    ActiveSheet.Calculate
     
     
     
     
     
    
     


End Sub

Thanks,
David

 

[Norie]

David

You really should get rid of all that Select/Activate/Selection stuff, doing so should not only speed things up it might help with your current problem.

For example this code,

   Workbooks("EXCELFILE.xlsb").Activate
    Worksheets("Solicitor Payments").Activate
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    
    Workbooks("EXCELFILE.xlsb").Activate
    Range("l3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents

can be rewritten like this.

    With Workbooks("EXCELFILE.xlsb").Worksheets("Solicitor Payments")
        .Range("A3", .Range("A3").End(xlToRight).End(xlDown)).ClearContents
        .Range("l3", .Range("l3").End(xlToRight).End(xlDown)).ClearContents
    End With

Similarly to copy the data from today's payments you could use this.

    Set wbPayments = Workbooks.Open(Path & "\" & Filename)
    
    With wbPayments.ActiveSheet
        .Range("A2").CurrentRegion.Copy
        Workbooks("EXCELFILE.xlsb").Sheets("Solicitor Payments").Range("A3").PasteSpecial Paste:=xlPasteValues
    End With

    Application.CutCopyMode = False

    wbPayments.Close SaveChanges=:False

 

[bruscus]

Thanks tidied up those bits it runs quicker however again breaks down when opening the first file - Could it be something with my macro settings?

 

[Norie]

Is there any code in the first file?

 

[bruscus]

There is but the issue seems to be a windows update issue - the updates get automatically blocked so have to pull them down for Excel to update.

Many thanks

 

[Norie]

If the code in the workbook(s) being opened is executed when they are opened, which could be the cause of the problem, you could temporarily disable events while running this code.

To do that add this at the top of the code,

Application.EnableEvents = False

and this at the end.

Application.EnableEvents = True