I think I know what’s causing this but am not sure what I can do (if anything) to fix it.
Someone in my department is opening two Excel files using the File-Open method from the Excel menu. When the list of files comes up she selects the two files she wants and clicks the Open button. The first file to open (which contains an Auto-Open macro) opens fine, then it starts opening the second file. When the second file tries to open, the Auto-Open macro from the first file errors out. This only happens when both files are opened from the File-Open menu. If they’re opened at the same time from Windows Explorer, the macro doesn’t error.
My theory is that the auto-open macro from the first file must still be running when the second file starts opening, then it can’t find the sheets & range names it’s looking for because the second file is the active spreadsheet.
If that’s the case, is there any way I can get the auto-open macro to finish before it starts opening the second file? I’m stumped.