Automation Error (?) while running Looped VBA Macro

Good Morning

While executing my looped Excel VBA code after around 10 runs of the loop all Excel workbooks that are currently open close without notification. There doesn't seem to be a pattern in this happening though, the number of loops it goes through is not constant and it sometimes shows automation error, and sometimes doesn't.

Unless it is necessary I'm not going to post the code yet because it is rather large and I can't isolate the problem. If there is a way to isolate the problem that would be great. I also know my code could be a lot cleaner but I am still learning a lot of the simple tricks regarding this.

Thank you

What action(s) is your loop performing?

This is the code here:

Please Login or Register  to view this content.

It takes a site name from a list and creates a new workbook with data from a single Excel database that relates to that site. Formats the new workbook, saves, and places it in an email if that option is selected.

This is the first Call

Please Login or Register  to view this content.

Then this runs

Please Login or Register  to view this content.

Then this:

Please Login or Register  to view this content.

And finally:

Please Login or Register  to view this content.

Originally Posted by Norie

Where does the code error?

That is one of the issues, I have no idea. It starts creating the workbooks without any issues, but once it gets to workbook ~10 it all closes. If you can suggest some way to isolate where the code errors that would be fantastic.

Thank you for the quick replies.

With your tips I've narrowed the crashes down to the MoveInfoFinal sub, but this is where it goes weird. I've placed a breakpoint at the line "Private Sub MoveInfoFinal()" and then hold down F5 to continue when it gets to that block and the whole thing runs fine and doesn't crash for the whole execution. Any idea of what is happening?

Thank you

I've tried this as well, still having the issues. Could the speed that the code is running at being the cause?

Thank you Norie, that cleaned code has seemed to fix the problem.