XLSM issue I cannot fix - Error 52...STUCK

Which button do you click when you get the error?

The emails should not be genuine. If they are real then  I would recommend requesting attention to have the example removed and create another example with fake emails.

Can you explain the operation in a little more detail.

I deleted the file and reloaded the file with the tab removed of the emails.

The problem happens when you are in the file and click on the "Generate Monthly Rep Statements" button.

Here is the error:
Run-time error '52':
Bad file name or number

Here's the code from the debug: (screenshot attached)
        SrtRow = J
        K = J
        Do
            K = K + 1
        Loop Until InStr(1, WSCURR.Cells(K, "A"), WSCURR.Cells(J, "A")) = 0
        EndRow = K - 1
       
        '---> Test to see if the rep found already have a workbook
        sRepFile = Dir(sFilePath & "\" & WSCURR.Cells(J, "A") & "-" & sMonth & ".xlsx")
        If sRepFile = "" Then
            '---> Create the new Workbook
            WSCURR.Copy
            Set WBREPS = ActiveWorkbook
            sRepFile = sFilePath & "\" & WSCURR.Cells(J, "A") & "-" & sMonth & ".xlsx"
            WBREPS.SaveAs Filename:=sRepFile
            Set WSREPS = ActiveSheet
error52debug.docx

There's lots of unnecessary code that I noticed, like this from the above code

Set WBREPS = ActiveWorkbook

Select allOpen in new window

this works just as well, but your code isn't causing the error.

WSCURR.Copy            
            sRepFile = sFilePath & "\" & WSCURR.Cells(J, "A") & "-" & sMonth & ".xlsx"
           ActiveWorkBook.SaveAs Filename:=sRepFile

Select allOpen in new window

I would suggest you check what is in the cell referred to here.

WSCURR.Cells(J, "A") & "-" & sMonth

Select allOpen in new window

In fact what is J referring to, it's a row number but is the cell empty at that point?

I am not versed enough in this to know. It works sometimes and not others any ideas?

I didn't write this.

If you don't know how to do what Roy suggests, or perhaps even if you do, then here is an article I wrote on debugging. Don't let the title put you off. Most of it also applies to VBA.

Roy- I can't speak to the code. It looks at three different files and within each file uses the SUBTOTALS worksheet from the workbook. It is set to break out each change in PriSlp and go from there and from each workbook it views create a file and/or worksheets as necessary for each different salesperson.

What I noticed when it runs is that it fails it is after it finishes processing 775 on the COMMISSIONS (section which is SA646) and it's at the Grand Total line I believe. It works in the file SA646_JUL18 (SUBTOTALS tab) but on the SA646_AUG18 (SUBTOTALS tab) it fails. I don't understand why that would be.

I will look at your article Martin Liss but I don't know that I will be able to figure it out.

It's odd because it appears that it is done creating the files for the COMMISSION and needs to move on to BOOKINGS as all the files are created in the holding folder that it generates.

When I debugged the code there were no errors. The error occurs when it is run.

I've put an error handler in that will hopefully show a message box with the value in the cell that I think is the problem. It is possibly empty or contains invalid characters.

Try using this workbook, see if you get a message beginning:

"This is the value: "
MonthlyRepCommissionStatementGenera.xlsm

Hmmm. I will try these. I came up with a work around in the SA646 file that runs first by adding 800 with nothing in it and then it kept running to the SA612 and the OP512 file.

Any updates Tracey?

Actually how I was able to make this work is that I added that line 800. I haven't had an opportunity to do anything else. My technical scope is quite limited.

I cannot believe that I missed the comment about the : in the field name....that is exactly my problem.

Thanks!