Run time error '1004': Copy method of worksheet class failed - Temp file issue

Question

The Problem

We have an excel vba macro we have been using to copy sheets into a new workbook to create a "report" version of a file for many years.

The code fails at this line of code:

ThisWorkbook.Worksheets(array_sheet).Copy

And throws the following errors:

File not found:'C:\Users\my_username\AppData\Local\Temp\VBXXXX.tmp'

Run-time error '1004': Copy method of Worksheet class failed

Possibly a profile/security setting issue

Recently, we brought on a new hire who is using Windows 7 and Excel 2010, which is what our other employees use without issue. New User experiences the issue, as do I on Windows 10, Excel 2016. I can also run the code successfully in a Citrix environment in Excel 2016. This leads me to believe it may be some type of security privilege/profile setting issue and is not related directly to the version of Windows or MS Office. I have admin privileges on my machine, where my coworker who is also experiencing the issue does not.

What I know so far

• In the code above we are copying a number of sheets at once, which was an attempted workaround which did not solve the error. Previously we were copying each sheet individually into the new workbook.

• Another odd feature of the error is that in the old code which copies each sheet individually, it does not error out on the same sheet every time, and rarely on the first sheet.

• The only solution we've found is to close and re-open excel, which solves the issue 100% of the time.

• The .tmp file has a different number each time and the .tmp file it is referencing is created during the process and exists if I let it hang or enter debug.

• We have tried adding a delay when copying sheets individually, but that does not resolve the error, nor does clearing out the referenced Temp directory.

• I have poured through the similar questions here on Stack Overflow and this is not a duplicate - the issue we are experiencing is unique from the other posts and is not fixed by the proposed solutions that are relevant.

Here is a table of our software/os setups and the status of the error:

| User        | OS/Software               | Status        |
|-------------|---------------------------|---------------|
| Old User    | windows 7/ office   2010  | works         |
| Old User    | citrix excel 2016         | works         |
| Me (new pc) | citrix excel 2016         | works         |
| New User    | citrix excel 2016         | works         |
|-------------|---------------------------|---------------|
| New User    | windows 7/ office   2010  | does not work |
| Me (new pc) | windows 10/ office   2016 | does not work |

Has anyone experienced a similar issue? Any proposed solutions?

Answer 1

1. Close all excel files, then re-open the macro file again, sometimes it works now.
2. If it does not work, try to remove the readonly property from the folder C:\Users\my_username\AppData\Local\Temp\, then try again.

Answer 2

I've heard some people say this is due to their anti-virus. I haven't been able to test that. My only other thought is maybe there is a Windows update that broke this VBA code, because this has just started happening to my code within the last few months.