Spreadsheet causing crashing

[Stildawn]

Hi All

I have a spreadsheet that is a bunch of invoices on separate sheets this comes from a client.

This spreadsheet (.xlsx) is stable by itself, can open it and use it etc.

Now I have a excel tool (.xlsm) I'm trying to import the individual sheets into, that's all good and the code runs, basically it just checks a sheet name on the original file then copies everything from that sheet into a new sheet in the tool of the same name, simple stuff.

However, once it does that the tool saves (via VBA) and has a msgbox saying its completed the import code, so far all good. But when the code is finished completely and I have control over the excel tool again, 8 times out of 10 the excel tool (.xlsm) will hang and often crash excel altogether.

If you reopen the tool, its all there including the imported sheets etc, and its now stable and doesn't crash excel.


So my question, what could possible be in these imported sheets that would be causing something like this? For example I know it has images and links and all that messy stuff computer systems put into generated spreadsheet, could that be the issue?

Thanks in advance.

 

[rollis13]

How is your import done? If it's done by Copy/Paste perhaps the issue could be solved by adding, just after the Msgbox line, a Application.CutCopyMode = False to free up some memory. If you attach your macro it would be very useful; then some analysis/testing could be done. A dummy file would be even better.

 

[Stildawn]

How is your import done? If it's done by Copy/Paste perhaps the issue could be solved by adding, just after the Msgbox line, a Application.CutCopyMode = False to free up some memory. If you attach your macro it would be very useful; then some analysis/testing could be done. A dummy file would be even better.

Here is the import code, its fairly straight forward (or at least I think it is):

ublic Function ImportSpreadsheet(strInvFile As String, strFile As String)
Dim strUnprocessedSheets As String

Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

strUnprocessedSheets = ""


Workbooks.Open strInvFile, UpdateLinks:=False

For i = 1 To Workbooks(strFile).Sheets.Count
    Select Case Workbooks(strFile).Sheets(i).Name
        Case Is = "XYZ 1"
            ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = "XYZ 1"
                        
            Workbooks(strFile).Sheets(i).Cells.Copy ThisWorkbook.Sheets("XYZ 1").Range("A1")
            

        Case Is = "ABC 1"
            ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = "ABC 1"
            
            Workbooks(strFile).Sheets(i).Cells.Copy ThisWorkbook.Sheets("ABC 1").Range("A1")
    
            
        Case Is = "XYZ 2"
            ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = "XYZ 2"
            
            Workbooks(strFile).Sheets(i).Cells.Copy ThisWorkbook.Sheets("XYZ 2").Range("A1")
          
        
        Case Is = "ABC 2"
            ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = "ABC 2"
                        
            Workbooks(strFile).Sheets(i).Cells.Copy ThisWorkbook.Sheets("ABC 2").Range("A1")
           
           
        Case Is = "XYZ 3"
            ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = "XYZ 3"
            
            Workbooks(strFile).Sheets(i).Cells.Copy ThisWorkbook.Sheets("XYZ 3").Range("A1")
          
          
        Case Else
            If strUnprocessedSheets = "" Then
                strUnprocessedSheets = Workbooks(strFile).Sheets(i).Name
            Else
                strUnprocessedSheets = strUnprocessedSheets & " / " & Workbooks(strFile).Sheets(i).Name & " / "
            End If
            
            
    End Select
Next i
        
Workbooks(strFile).Saved = True
Workbooks(strFile).Close

Application.AskToUpdateLinks = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

If strUnprocessedSheets = "" Then
    'Empty
Else
    lngInstr = InStr(1, strUnprocessedSheets, " / ")
    If lngInstr = 0 Then
        'No " / "
    Else
        strUnprocessedSheets = Left(strUnprocessedSheets, Len(strUnprocessedSheets) - 3)
    End If
        
    MsgBox "Spreadsheet imported successfully." & vbNewLine & vbNewLine & "However the below sheets were not imported:" & vbNewLine & vbNewLine & strUnprocessedSheets, vbInformation, "Import Compelted"
End If

End Function

Don't really want to attached copy of the spreadsheet as its a well known brand/client etc with sensitive information.

What does Application.CutCopyMode = False do, as yes there is a lot of copy/paste going on so that could be it.

Thanks

 

[rollis13]

Don't really want to attached copy of the spreadsheet as its a well known brand/client etc with sensitive information.

That's why I said "a dummy file". It was only to realize the structure of the sheets to be copied.

What does Application.CutCopyMode = False do, as yes there is a lot of copy/paste going on so that could be it.

Here is some information for you: LINK

 

[Stildawn]

That's why I said "a dummy file". It was only to realize the structure of the sheets to be copied.

Here is some information for you: LINK

I'm simply copying the whole sheet (via the .Cells.Copy method), does the structure matter?

So reading that (and other googling) I should be putting "Application.CutCopyMode = False" after every copy function to clear the memory?

Or is there a generally more efficient copying method than "Workbooks(strFile).Sheets(i).Cells.Copy ThisWorkbook.Sheets("XYZ 3").Range("A1")"

As I only need the data (so values) is there a better way?

For example in reading around, I found these examples:

Sub CopyAndPasteValuesOnly()

ActiveSheet.Range("A1:A20").Value = ActiveSheet.Range("B1:B20").Value

End Sub

Sub CopyAndPasteValuesOnly()

ActiveSheet.Range("A1:A20").Copy Destination:=ActiveSheet.Range("B1:B20")

End Sub

Are these two methods more efficient? How would I write them to include ALL cells in the source sheet?

 

[rollis13]

If you were to copy only values maybe I would cut down the range with .UsedRange LINK instead of all the cells of sheet and then use .PasteSpecial Paste:=xlPasteValues LINK to paste only values.

 

[Stildawn]

If you were to copy only values maybe I would cut down the range with .UsedRange LINK instead of all the cells of sheet and then use .PasteSpecial Paste:=xlPasteValues LINK to paste only values.

Thanks, I have now done the code like this:

Workbooks(strFile).Sheets(i).UsedRange.Copy
ThisWorkbook.Sheets("XYZ 1").Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

And that seems so much quicker, and haven't had a crash yet, but will run it a bunch more times to check.

Much appreciated.

 

[rollis13]

Yes, that's it, give that code a try. It probably could help to fix you issue but as you may understand, without any idea on how your project is structured, it's just a guess.
Anyway, thanks for the feedback , glad having been of some help ... if any.