Advanced data filtering on different sheet

[John]

When using advanced filtering with copy to another location, can you copy to
a different sheet? I have a sub procedure that does just that and it fails
occassionally with the following error messages:

You can only copy filtered data to the active sheet.
or
AdvancedFilter method of range class failed.

I don't understand why it works soometimes and fails other times. Is there a
workaround?

 

[Nigel]

Advance filter will only allow the filtered result to be located on the
activesheet. To create a reliable solution put the result in a convenient
area on the active sheet then copy it to your final destination. If
necessary delete the result data on the activesheet

 

[Patrick Molloy]

not so

If your table is range named, then you can certainly put the results of the
advanced filter on another sheet.

activate the sheet where you want the results (NOT the sheet with the table).
Ensure that you have your criteria / outout headings ready too, then select
from the menu Data/Filter/AdvancedFilter

In the field 'List Range:' of the Advanced Filter form, just type in the
range name of the table