Category: Excel Filter

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.

__________________________

It seemed simple enough, but counting the AutoFilters on an Excel sheet is a tough job!

The answer to “How many worksheet AutoFilters are there?” is “It depends!”

You can read the fascinating (to me!) results below.

The Good Old Days

In the old days, before Excel 2003, you could only have one AutoFilter on a sheet. That’s pretty easy to count – either 1 or 0.

For example, this Excel 2010 sheet has a single list that is not a named table. An AutoFilter was applied to this list, and the AutoFilter arrows have been turned off.

If you point to one of the headings, where the hidden arrow is, the tooltip for that filter appears, so that shows us the AutoFilter is still active.

Count the Worksheet AutoFilters

To count the worksheet AutoFilters, I usually use AutoFilterMode to check if one exists.

Recently, Pascal (forum name: p45cal) emailed me, to suggest that checking for a worksheet AutoFilter would be more reliable. Thanks, Pascal, for inspiring this test!

Macro Code Count AutoFilters

This code tests for a worksheet AutoFilter, by using either AutoFilterMode or AutoFilter:

Sub CountSheetAutoFilters()
Dim iARM As Long
Dim iAR As Long
'counts all worksheet autofilters
'even if all arrows are hidden
If ActiveSheet.AutoFilterMode = True Then iARM = 1
    Debug.Print "AutoFilterMode: " & iARM
If Not ActiveSheet.AutoFilter Is Nothing Then iAR = 1
    Debug.Print "AutoFilter: " & iAR
End Sub

When I test that code in the Immediate window, both counting methods show 1 AutoFilter.

Named Table on the Worksheet

What happens if there is a named table on the worksheet, and it has its own AutoFilter? I ran the code again, on the worksheet shown below.

The worksheet has a named table, and it has an AutoFilter applied, with all the arrows hidden.

When I test the code in the Immediate window, both counting methods show zero AutoFilters.

I consider that count correct, because there is a ListObject with an AutoFilter, but no worksheet AutoFilter.

Different Count Results

In the screen shot above, you can see that cell A1 is selected – outside of the named table. When I selected cell B1, inside the table, and ran the code, the results were different. AutoFilterMode was still zero, but AutoFilter detected one.

Apparently, Excel is counting the active cell’s table as a worksheet AutoFilter, with the AutoFilter counting method. I’d rather go with the AutoFilterMode’s zero, and count the ListObject AutoFilters separately.

Test With Visible AutoFilter Arrows

Maybe the hidden arrows are affecting the results. To check, I ran code to show all the list AutoFilter arrows, and tested again.

The results were the same as in the previous tests, so visible arrows don’t make a difference.

Multiple Tables on Worksheet

For some final tests, I created a sheet with 3 lists:

1. Named Table – no AutoFilter – no arrows
2. Named Table – AutoFilter – visible arrows
3. Worksheet table – AutoFilter – visible arrows

With a cell in Named Table 1 selected, AutoFilterMode counted one, and AutoFilter counted zero.

As in the previous test, the AutoFilter counting method is based on the active cell’s table AutoFilter. It doesn’t detect the AutoFilter in Worksheet Table 3.

And More Results

With any other cell in the worksheet selected, the results were different – both AutoFilter and AutoFilterMode counted one – the correct count of worksheet AutoFilters.

Counting Worksheet AutoFilters Conclusion

Because ActiveSheet.AutoFilter detects the AutoFilter in the active cell, it could cause a miscount of worksheet AutoFilters.

I’ll stick to the AutoFilterMode for a count of worksheet AutoFilters, and use other code to count the ListObject AutoFilters.

AutoFilters in Other Excel Versions

After running these tests in Excel 2010, I tested the AutoFilter counting code in Excel 2003, and got the same results.

If you find different results in other versions of Excel, please let me know.

___________