Good macro crashes after apparently unrelated event

[Jeff Ho]

I posted this on another forum, here, but there was no solution. I’d like to see if someone here might have an idea.

I have a workbook with one macro that produces individual volunteer activity reports and another one that saves a separate Monthly Report as a single-sheet xlsx workbook for submitting to regulators. Both macros work fine.

The problem is that after opening the saved Monthly Report saved by macro 2, macro 1 causes Excel to crash. It makes no sense.

Macro 1 generates one or several individual activity reports and saves them in a designated folder as xlsx workbooks. The user has the option to view each report immediately or simply save them to be opened later.

Macro 2 takes a monthly report which was generated by a separate procedure and saves it in a different designated folder as an xlsx workbook. Since the user sees the original report in the primary workbook, they don’t have the option to view the saved workbook via macro 2. In addition, I remove all macro buttons and even the range names in the saved xlsx.

I can run macro 2 and then run macro 1 successfully. But if I open the monthly report saved by macro 2, then macro 1 crashes the system.

If anyone has an idea or would like to explore the problem, I can explain more and upload the workbook, so let me know. Meanwhile, it’s already available on Eileen’s Lounge at the link above, along with the ideas we already looked at.

Thanks,
- Jeff

[Keebellah]

I think everybody's guess is just as cloudy as mine. Macro 1 and Macro 2 perfect descriptions but they're not telling the story. What's IN the macro
Without that we'll all be assuming and I don't know what assume does to people, it makes an A S S out of U and ME, don't take it personal, it's just to make you think.
The only thing I can imagine at this stage without a sample of the main file that creates that report is that in one way or another they trigger each other.
My suggestion: attach the file (dummy data please) so that someone can test and try an reproduce it, THEN maybe you will be able to get an answer.
Are all the users using Office 365?

[Jeff Ho]

Thanks for your interest, Keebellah. I’ve attached the workbook with test data. In fact, there is no real data yet. I’m just developing the beta to show the organization next week. Everything works ok except for this crazy glitch that I've just discovered. Here’s what you need to replicate the issue:

Macro 1 is ‘VolRpt_Single’ (misnamed because it actually produces single and multiple reports). It’s called with the ‘Volunteer Activity’ button on the ‘Activity Table’ sheet.

Macro 2 is ‘PublishReport’. It’s called with the ‘Publish Rpt.’ button on the ‘Monthly Report’ sheet.

You’ll need two folders on your desktop: ‘Monthly Reports’ and ‘VolReports’. That’s where the xlsx copies are saved. If you want to save them elsewhere, search the word “environ” in the two macros.

Procedure:
1. Run VolRpt_Single to see how it works. You can use any VID in the Activity Table having a date within the reporting month you select, or "Get all reports".
2. Run PublishReport to see how it works. (You can change the monthly report with the ‘Run Report’ button on either the ‘Activity Table’ or ‘Monthly Report’ sheets. There is data for both reports in December 2018, March 2019, and April 2019.)
3. Go to the Monthly Reports folder on your desktop and open the xlsx workbook that was created. Then close it.
4. Now run VolRpt_Single again and Excel should freeze.

And, yes, I’m using Office 365.

Good luck. Hope you find the trigger.

P.S. Just curious about posting here. Why does my original text disappear when I click 'Preview'? Then I have to copy and paste the text from the preview back into the editing window in order to post. Is there a setting for this somewhere?

[Keebellah]

Got the file, will see what I can do for you and see if I can detect the reason
I don't have 365 but will test with 2016

[Keebellah]

Strange, very strange, however, it only crashes if you open the newly created Month report if the main file is still open.
If you close all open files and the ope the newly create month report, close it and repeat the procedure, no problems.
What I did notice is that the new month report contains a many cells with #NAME errors because the data / reference is not found.
The Master file has missing named ranges, I attached the list of all the named ranges as they are now so you can see for yourself.

You have hard-coded target folders unless you also have a routine to create the destination sub-folders in the required location when needed.

I also personally never use the Desktop but that's personal

[Jeff Ho]

Thanks for taking a look, Keebellah. And especially for pointing out about the broken name references! One of my last-ditch efforts to find a connection was to add code which removed all range names from the newly created xlsx Monthly Report. I forgot to look at the stats which are calculated using range names.

What's more, that gives me an idea for a work around. Maybe instead of copying the Monthly Report worksheet I can make a new workbook and copy the data in as text. That might simplify the process and possibly eliminate the crash-response.

I'd rather not advise the users to close their primary workbook before opening the published report. If the copy-paste idea doesn't pan out, I'll come up with some other work around.

Anyway, I appreciate the time you and Hans V from Eileen's Lounge put into checking it out. I really expected it was going to be some silly and obvious thing I had overlooked (like removing all range names without checking the stats!).

Thanks,
- Jeff

[Keebellah]

If I do come across something I'll let you know, but so far no idea, the idea to create a new workbook and copy as text is okay but you can also use the same idea that once you created the new workbook, select all cells and then paste back values only, should give the same result, only make sure you do that in the newly created workbook.