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

1
votes

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'

enter image description here

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

enter image description here

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?

2
vbaexcel
have you tried a vb cleaner? Clean out the project, export all modules and import in again? Though I guess this wouldn't account for some of the behaviour you describe. - QHarr
@QHarr I'm not positive I know what you mean by vb cleaner/cleaning out the project. Do you mean installing some code to clear out the temp folder? - nothingtwisted
Is your variable (array_sheet) equal to the path and filename of the file not found? If so, does that file exists when it errors out? Try to copy to or from a file that doesn't exist will give you the 1004 error. Look at how you are creating the temp file and if it is different for your configurations. - mooseman
@mooseman array_sheet is a string array populated with the names of the sheets to copy from the original workbook. The temporary file is being automatically created by excel during the process and exists if I go look once I get the error but I am not referencing it specifically. - nothingtwisted
Is thisworkbook actually referencing the temp file that you expect it to? It just guess work for us here with the little bit of code. Can you F8 through this part and check your references and variables to see where it's going off the rails. - mooseman

2 Answers

2
votes

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.

0
votes
  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.