“Run-time error ‘1004’: Copy Method of Worksheet Class failed” “Run-time error ‘1004’: Application-defined or object-defined error”
The exact error message you see might also vary slightly, in a few cases, although the error code will remain the same. Regardless of which version of the issue you experience, however, it almost always occurs while running a macro in Excel that is designed to copy worksheets and then place the copies into the same workbook as the original worksheet. Unlike the worst tech problems out there, the cause of Run-time error 1004 within Microsoft Excel is known. Run-time error 1004 occurs when the macro you are running is copying the original worksheet to a workbook with a defined name that you did not save and close before you ran the macro. An example of such a macro can be seen in the following code: Since the cause of Run-time error 1004 in the context of Microsoft Excel is known, as is the resolution. In addition to a solution to this issue, what is also available to users affected by it is a workaround that can be used in the event that the solution doesn’t work or they don’t find the solution to be a viable option.
The Solution:
The solution to this specific problem is to simply edit the code of the macro you are running to have it periodically save and close the target workbook while it makes copies of the worksheet(s). The code for a macro that does this would look something along the lines of the following: Note: The exact number of times you can copy of a worksheet before you need to save and close the workbook the copies are being saved to varies from one case to the next as it depends on the size of the worksheet you are making copies of.
The Workaround:
As stated before, a workaround to this specific problem also exists. Working around this issue is a simple matter of inserting a new worksheet from a template instead of creating a copy of an existing worksheet. If you would like to work around this problem, here’s what you need to do: Note: In the line of code described above, path\filename needs to be replaced with the full path (inclusive of the complete file name) for the location of the sheet template you have just created.
How to Fix a Sharing Violation Error in ExcelHow to Fix ‘An Array Value Could not be Found’ Error on ExcelHow to Fix “#Spill Error” in Microsoft Excel?How to Fix #Name Error in Excel (with Examples)
