Macro Just Stop Halfway Through

[epoiezam]

Hi Guys,

Hope you can get me out of this mess. Suddenly my macro starts to give me headache.
It suppose to go through all xlsx files in multiple subfolders and run some activities. But not sure why but lately each time it stop halfway just like that.
No error no nothing.

One thing I realize, is that the subfolders files size had increase significantly from day one.

'==============================

Private Sub CommandButton1_Click()

Dim mainFolder As String

With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select the main folder"
If Not .Show Then Exit Sub
mainFolder = .SelectedItems(1)
End With

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Change_Workbooks_In_Folder mainFolder

Application.ScreenUpdating = True
Application.DisplayAlerts = True

MsgBox "Done"

End Sub


Private Sub Change_Workbooks_In_Folder(folderPath As String)

Static FSO As Object
Dim Folder As Object, Subfolder As Object, File As Object
Dim wb As Workbook
Dim pass As String

pass = UserForm1.TextBox1.Value

If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")

Set Folder = FSO.GetFolder(folderPath)

For Each File In Folder.Files
If File.Name Like "*.xlsx*" Then
Set wb = Workbooks.Open(File.Path)

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets.Add.Name = "Lead Referrals"
ActiveSheet.Paste
Selection.Columns.AutoFit

Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws.Name <> "Lead Referrals" Then ws.Delete
Next ws

Range("A1").Select

wb.SaveAs ActiveWorkbook.FullName, Password:=pass
Application.DisplayAlerts = True
ActiveWorkbook.Close


End If
Next


For Each Subfolder In Folder.SubFolders
Change_Workbooks_In_Folder Subfolder.Path
Next

End Sub

 

[Zot]

Please update profile to show your Excel version and save it.

Probably not enough RAM since it was running fine before and you mentioned the file gets bigger and bigger. My problem is that the Excel crashed whenever I tried to save big file. On other PC it was okay. Well, my office desktop has only 4GB of RAM

 

[epoiezam]

Managed to find the error.. Thank you for trying to help out..

 

[smozgur]

Good to hear you managed to find the error. Future readers might also find it helpful if you could share it on the board.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.