VBA - Random Errors when saving multiple workbooks from a loop

BigShango

Board Regular
Joined
May 8, 2014
Messages
103
I'm at a loss with this one. I have a piece of code running down a list of names and saving a new workbook, with some info, for each name. It appears to fail randomly when trying to save. Like, 1st try it will fail at line 21 so I'll restart it from line 21 and it will run fine until say line 52. Next try (with no changes to the code) it will run until line 33 etc. It always fails on saving the workbook.

I've included the full code other than the save filepath as it had personal info in it.

Code:
Sub CreateBooks()

Set MB = Workbooks(ActiveWorkbook.Name)
Set M = MB.Sheets("Managers")
Set A = MB.Sheets("Sheet1")


For MRow = 1 To M.Range("A" & M.Rows.Count).End(xlUp).Row
    
    Manager = Trim(M.Range("A" & MRow).Value) & " " & Trim(M.Range("B" & MRow).Value)
    MB.Sheets("Awaiting Sign-Off").Copy
    Set NB = Workbooks(ActiveWorkbook.Name)
    Set N = NB.Sheets("Awaiting Sign-Off")
    
    For ARow = 2 To A.Range("A" & A.Rows.Count).End(xlUp).Row
                    
        If LCase(Trim(A.Range("D" & ARow).Value) & " " & Trim(A.Range("E" & ARow).Value)) = LCase(Manager) Then
            PasteRow = N.Range("A" & N.Rows.Count).End(xlUp).Offset(1).Row
            N.Range("A" & PasteRow).Value = A.Range("A" & ARow).Value
            N.Range("B" & PasteRow).Value = A.Range("B" & ARow).Value
            N.Range("C" & PasteRow).Value = A.Range("H" & ARow).Value
        End If
        
    Next ARow


    NB.SaveAs Filename:="R:\x" & Manager & ".xlsx", FileFormat:=xlOpenXMLWorkbook
    NB.Close Savechanges = True
    
Next MRow


End Sub

Any idea why this fails at random lines? The error is 'SaveAs method of workbook class failed'. It got all the way to line 61 before giving an error on the latest run. The folder that it is saving to is completely empty and there are no duplicate names listed.

Thanks
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Watch MrExcel Video

Forum statistics

Threads
1,122,712
Messages
5,597,716
Members
414,166
Latest member
Donerightdata

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top