Results 1 to 6 of 6

Thread: VBA: Excel has stopped working
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA: Excel has stopped working

    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

    Code:
    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

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,140
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA: Excel has stopped working

    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,
    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.
    Code:
        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.
    Code:
        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
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Jul 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Excel has stopped working

    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?

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,140
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA: Excel has stopped working

    Is there any code in the first file?
    If posting code please use code tags.

  5. #5
    New Member
    Join Date
    Jul 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Excel has stopped working

    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

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,140
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA: Excel has stopped working

    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,
    Code:
    Application.EnableEvents = False
    and this at the end.
    Code:
    Application.EnableEvents = True
    If posting code please use code tags.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •