Advanced Filter whit named ranges macro (Error 1004)

[Caliche]

In the following macro I am getting 1004 error (Failure in Advanced Filter Method of Range Class). I have been told that for Advanced Filter your ranges must be in the same workbook, and that you can avoid this situation using named ranges, as in my case, where criteria and "copy to" ranges are in a workbook (Disp_Dem.xls in OF worksheet), and data range is in another workbook (Trabajo.xls).

Here is my code.

Sub Cons_Ord()
'
' Order Filter
'
Workbooks("Disp_Dem").Activate
Worksheets("OF").Select
Range("Crit_Lanz").Select
Range("Trabajo.xls!Datos_lanz").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("Disp_Dem.xls!Crit_Lanz"), CopyToRange:=Range("Disp_Dem.xls!Rango_Lanz"), Unique:=False

End Sub

Any suggestions in order to avoid this error?.

Thanks in advance.

 

[Mark W.]

On 2002-04-24 11:33, Caliche wrote:
In the following macro I am getting 1004 error (Failure in Advanced Filter Method of Range Class). I have been told that for Advanced Filter your ranges must be in the same workbook, and that you can avoid this situation using named ranges...

While it's true that the "Copy to" reference must be on the same worksheet when you're using Excel's Data | Filter | Advanced Filter... menu command, it's not the case for a macro. You don't need an named range.

 

[Jack in the UK]

error 1004 is common, i guess down to the named ranges that refer to in VBA, this error is conflict of some kind..