VBA Freezing

[ExcelNewbie_2023]

I have a macro running to save workbooks from my current work book based on a list. It will run and save all workbooks until it gets to the last name in the list and I get a "Save Failed" and excel freezes. All other files save just fine, it is only when it reaches the last populated cell in the list that it freezes. I have to use task manager to force Excel to close.

Sub WorkBook_Generation()
'
' Create Workbooks From Audit Template with names stored in Column AG

 Dim fName As Range
 
  For Each fName In Range("AG1", Cells(Rows.Count, "AG").End(xlUp))
 
   On Error Resume Next
  
   ThisWorkbook.SaveAs Filename:=fName.Value, FileFormat:=50
  
  Next fName
End Sub

 

[MARK858]

All other files save just fine, it is only when it reaches the last populated cell in the list that it freezes

So what exactly is the text in the last cell?

 

[ExcelNewbie_2023]

Below are some list examples of when I have ran the macro. It does not seem to matter how long my list in the column is. When it reaches the last cell it freezes.

699H46HP0209

700H55HP0003

701H55HP0002

702H00GP0108

70323JTKZDM3

704H47HP0169

705H47HP0173

706H53HP0052

707H55HP0006

708H00GP0109

709H47HP0170

70323JTKZDM3

704H47HP0169

705H47HP0173

706H53HP0052

707H55HP0006

708H00GP0109

709H47HP0170

710H55HP0005

71123JTKZDN8

712H46HP0197

71323JTK4005

71423JTK4020

712H46HP0197

71323JTK4005

 

[MARK858]

On a test sheet Copy/Paste the range, delete the blank rows, remove the On Error Resume Next line and run the code.
What error message (if any) do you get?

Have you tried putting in a file path?

 

[Joe4]

Do you have blank cells in the middle of your data?
If so, that would be an issue, because you cannot name a file with a blank name.

I would also recommend temporarily removing the:

On Error Resume Next

line.
It is really hard to debug if you are telling it to ignore all the errors. You won't get the message of what it is having problems with.

Maybe try something like this:

Sub WorkBook_Generation()
'
' Create Workbooks From Audit Template with names stored in Column AG

    Dim fName As Range
 
    On Error GoTo err_chk
 
    For Each fName In Range("AG1", Cells(Rows.Count, "AG").End(xlUp))
'       Check to see if a value in field before saving
        If fName.Value <> "" Then
           ThisWorkbook.SaveAs Filename:=fName.Value, FileFormat:=50
        End If
    Next fName
  
  Exit Sub
  
  
err_chk:
    MsgBox "Error with cell " & fName.Address & vbCrLf & Err.Number & ":" & Err.Description
    
End Sub

If you get an error message, tell us exactly what message is being returned, and tell us exactly what appears in the cell address the error message is referring to.

 

[ExcelNewbie_2023]

On a test sheet Copy/Paste the range, delete the blank rows, remove the On Error Resume Next line and run the code.
What error message (if any) do you get?

Have you tried putting in a file path?

Sub WorkBook_Generation()
'
' Create Workbooks From Audit Template with names stored in Column AG

 Dim fName As Range
 
  For Each fName In Range("AG1", Cells(Rows.Count, "AG").End(xlUp))
 
   ThisWorkbook.SaveAs Filename:=fName.Value, FileFormat:=50
 
  Next fName
End Sub

Running on a test sheet has no issues. I believe I have blanks in the

Do you have blank cells in the middle of your data?
If so, that would be an issue, because you cannot name a file with a blank name.

I would also recommend temporarily removing the:

On Error Resume Next

line.
It is really hard to debug if you are telling it to ignore all the errors. You won't get the message of what it is having problems with.

Maybe try something like this:

Sub WorkBook_Generation()
'
' Create Workbooks From Audit Template with names stored in Column AG

    Dim fName As Range
 
    On Error GoTo err_chk
 
    For Each fName In Range("AG1", Cells(Rows.Count, "AG").End(xlUp))
'       Check to see if a value in field before saving
        If fName.Value <> "" Then
           ThisWorkbook.SaveAs Filename:=fName.Value, FileFormat:=50
        End If
    Next fName
 
  Exit Sub
 
 
err_chk:
    MsgBox "Error with cell " & fName.Address & vbCrLf & Err.Number & ":" & Err.Description
   
End Sub

If you get an error message, tell us exactly what message is being returned, and tell us exactly what appears in the cell address the error message is referring to.

You are a lifesaver. Thank you so much!

 

[ExcelNewbie_2023]

On a test sheet Copy/Paste the range, delete the blank rows, remove the On Error Resume Next line and run the code.
What error message (if any) do you get?

Have you tried putting in a file path?

This put me on the right path in helping identify the error. Thank you for your time!

 

[Joe4]

You are welcome.
Glad we were able to help.