CODEWITHSUNDEEP
excelvbaperformanceoptimization
TKE-439
TKE-439

Reputation: 87

Erase data stored in memory or simplified this sub further?

I'm having an issue somewhere in this sub when I run it. I call it up through another sub, but my entire pc crashes somewhere during this routine.

1- is there a way to simplify it further than I already have, and

2- I can't find the command to erase anything stored in memory before this point or somewhere I can insert something similar in this code if it is appropriate to do so.

Sub Splitbook()
Dim xPath As String, xWs As Worksheet, Box As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Box = Application.InputBox("Set?")

For Each xWs In ActiveWorkbook.Sheets
    If xWs.Name <> "Master" Then
        xWs.Copy
        Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & " " & Box & ".xlsx"
        Application.ActiveWorkbook.Close False
    Else

    End If
Next

Application.DisplayAlerts = True
End Sub

Sorry in advance if this isn't an appropriate topic.

Upvotes: 0

Views: 41

Answers (1)

Peicong Chen
Peicong Chen

Reputation: 317

I suggest you try using VBA.DoEvents. the problem you have may due to using lots of system memory since you are opening and close excel workbook. When you have too many workbooks in circulation your original excel becomes "not responding" using VBA.DoEvents may slow down overall speed but should preventing the original workbook to be "not responding"

For Each xWs In ActiveWorkbook.Sheets
If xWs.Name <> "Master" Then
vba.DoEvents
    xWs.Copy
    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & " " & Box & _
".xlsx"
    Application.ActiveWorkbook.Close False
Else

End If
Next

Upvotes: 1

Related Questions