It should have been a simple task in Excel VBA – copy a filtered range, and paste it into a new workbook. How many times have you written code to do that, and it always runs without problems?
However, last week a client sent me a file where that simple code wasn't working. While copying and pasting the filtered range, an error message kept popping up:
Run-time error '1004': Paste method of Worksheet class failed.
Look For the Obvious Problems
I figured there was some simple and obvious reason for the error, and went through the code, looking for problems. I tweaked a few lines in the code, where there were ambiguous references, but nothing helped. That annoying error message kept popping up. And, ,to add to the confusion, the data had been copied onto the worksheet, despite the error message.
A Google search was fruitless – there were many people complaining about similar problems, but no solutions that appeared to work. At least there weren't any solutions that I could find.
Most of the suggestions were to change the order of the steps, because Excel might be losing the copied data, before it could paste. I tried that too, and it didn't change anything.
External Ranges in the Filtered Data
Finally, I noticed that there were External Data ranges in the sheet where the filtered data was located. It seemed unlikely, but maybe those ranges were interfering with the copy and paste. So, I deleted those names, and tried the macro again. Amazingly, it worked!
I added code to delete those ranges as part of the macro, in case more External Data ranges are added to the data in the future.
Code to Delete Named External Ranges
In my client's workbook, all the external data range names started with "ExternalData_", so I used that to find the ranges and delete the names.
Here is the bit of code that I added to the top of the macro.
Dim nm As Name For Each nm In ThisWorkbook.Names If InStr(nm.Name, "ExternalData_") > 0 Then nm.Delete End If Next nm
The Problem Comes Back
Unfortunately, after running the revised macro for a while, the error message came back, even though the external data ranges had been deleted. Maybe there were new ranges, that had different names, or perhaps deleting the external data ranges was just a temporary fix.
My client added an "On Error Resume Next" line to the macro, to get past the problem section, and that's working fine for now. As a better solution, I suggested using an Advanced Filter to extract the data to the new workbook, because is very fast (except in Excel 2007), and doesn't create the same error message.
You can record a macro as you manually run an Advanced filter and then tweak the code to make it flexible. See the steps and a video in this blog post: Advanced Filter Macro
Still a Problem in Excel 2010
The workbook that my client sent was created in Excel 2003, and that's were I did the testing, and found the solution.
To see if the problem was fixed in newer versions of Excel, I tested the workbook in Excel 2007 and Excel 2010. If the External Data ranges were not deleted, the same error message appears when you run the copy and paste code.
So, if you run into this error message, and none of the obvious solutions help, check for External Data, and delete those ranges, if possible. If you use this solution, please let me know if it works permanently, temporarily, or not at all.