Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Advanced Filter whit named ranges macro (Error 1004)

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Colombia
    Posts
    339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •