+ Reply to Thread
Results 1 to 6 of 6

Macro Error...I Give Up

  1. #1
    documike
    Guest

    Macro Error...I Give Up

    Happy New Year everyone!

    I am trying to run Data/Advanced Filter & copy filter result to another
    worksheet. When I manually do it, it works fine, however when I record a
    macro and run it, I get "Run time error '1004' Extract name has a missing or
    illegal field name.
    Any ideas? Here's the macro:

    Sub CopyFilterData2()
    '
    ' CopyFilterData2 Macro
    ' Macro recorded 1/1/2005
    '

    '
    Range("V7").Select
    Sheets("Entry").Range("A8:N31").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Entry").Range("L3:L4"),
    CopyToRange:=Range("I19:T29" _
    ), Unique:=False
    ActiveWindow.ScrollColumn = 1
    Range("B8").Select
    End Sub



  2. #2
    Tom Ogilvy
    Guest

    Re: Macro Error...I Give Up

    Just designate the upper left corner of the destination range:

    Sub CopyFilterData2()
    '
    ' CopyFilterData2 Macro
    ' Macro recorded 1/1/2005
    '

    '
    Range("V7").Select
    Sheets("Entry").Range("A8:N31").AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Entry").Range("L3:L4"), _
    CopyToRange:=Range("I19"), _
    Unique:=False
    Range("B8").Select
    End Sub

    This worked for me.

    --
    Regards,
    Tom Ogilvy


    "documike" <[email protected]> wrote in message
    news:2vBBd.55963$k25.37798@attbi_s53...
    > Happy New Year everyone!
    >
    > I am trying to run Data/Advanced Filter & copy filter result to another
    > worksheet. When I manually do it, it works fine, however when I record a
    > macro and run it, I get "Run time error '1004' Extract name has a missing

    or
    > illegal field name.
    > Any ideas? Here's the macro:
    >
    > Sub CopyFilterData2()
    > '
    > ' CopyFilterData2 Macro
    > ' Macro recorded 1/1/2005
    > '
    >
    > '
    > Range("V7").Select
    > Sheets("Entry").Range("A8:N31").AdvancedFilter Action:=xlFilterCopy, _
    > CriteriaRange:=Sheets("Entry").Range("L3:L4"),
    > CopyToRange:=Range("I19:T29" _
    > ), Unique:=False
    > ActiveWindow.ScrollColumn = 1
    > Range("B8").Select
    > End Sub
    >
    >




  3. #3
    documike
    Guest

    Re: Macro Error...I Give Up

    Yes, but it then only copies the 1st column to the destination...I have 12
    columns


    "Tom Ogilvy" <[email protected]> wrote in message
    news:uQ%[email protected]...
    > Just designate the upper left corner of the destination range:
    >
    > Sub CopyFilterData2()
    > '
    > ' CopyFilterData2 Macro
    > ' Macro recorded 1/1/2005
    > '
    >
    > '
    > Range("V7").Select
    > Sheets("Entry").Range("A8:N31").AdvancedFilter _
    > Action:=xlFilterCopy, _
    > CriteriaRange:=Sheets("Entry").Range("L3:L4"), _
    > CopyToRange:=Range("I19"), _
    > Unique:=False
    > Range("B8").Select
    > End Sub
    >
    > This worked for me.
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "documike" <[email protected]> wrote in message
    > news:2vBBd.55963$k25.37798@attbi_s53...
    >> Happy New Year everyone!
    >>
    >> I am trying to run Data/Advanced Filter & copy filter result to another
    >> worksheet. When I manually do it, it works fine, however when I record a
    >> macro and run it, I get "Run time error '1004' Extract name has a missing

    > or
    >> illegal field name.
    >> Any ideas? Here's the macro:
    >>
    >> Sub CopyFilterData2()
    >> '
    >> ' CopyFilterData2 Macro
    >> ' Macro recorded 1/1/2005
    >> '
    >>
    >> '
    >> Range("V7").Select
    >> Sheets("Entry").Range("A8:N31").AdvancedFilter Action:=xlFilterCopy,
    >> _
    >> CriteriaRange:=Sheets("Entry").Range("L3:L4"),
    >> CopyToRange:=Range("I19:T29" _
    >> ), Unique:=False
    >> ActiveWindow.ScrollColumn = 1
    >> Range("B8").Select
    >> End Sub
    >>
    >>

    >
    >




  4. #4
    Dave Peterson
    Guest

    Re: Macro Error...I Give Up

    In my simple testing, it copied all the columns.

    Any chance that the criteria range specifies rows that are empty in B:N???

    (In case it isn't that, what did you have in L3:L4 of Entry--for testing
    purposes.)



    documike wrote:
    >
    > Yes, but it then only copies the 1st column to the destination...I have 12
    > columns
    >
    > "Tom Ogilvy" <[email protected]> wrote in message
    > news:uQ%[email protected]...
    > > Just designate the upper left corner of the destination range:
    > >
    > > Sub CopyFilterData2()
    > > '
    > > ' CopyFilterData2 Macro
    > > ' Macro recorded 1/1/2005
    > > '
    > >
    > > '
    > > Range("V7").Select
    > > Sheets("Entry").Range("A8:N31").AdvancedFilter _
    > > Action:=xlFilterCopy, _
    > > CriteriaRange:=Sheets("Entry").Range("L3:L4"), _
    > > CopyToRange:=Range("I19"), _
    > > Unique:=False
    > > Range("B8").Select
    > > End Sub
    > >
    > > This worked for me.
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > >
    > > "documike" <[email protected]> wrote in message
    > > news:2vBBd.55963$k25.37798@attbi_s53...
    > >> Happy New Year everyone!
    > >>
    > >> I am trying to run Data/Advanced Filter & copy filter result to another
    > >> worksheet. When I manually do it, it works fine, however when I record a
    > >> macro and run it, I get "Run time error '1004' Extract name has a missing

    > > or
    > >> illegal field name.
    > >> Any ideas? Here's the macro:
    > >>
    > >> Sub CopyFilterData2()
    > >> '
    > >> ' CopyFilterData2 Macro
    > >> ' Macro recorded 1/1/2005
    > >> '
    > >>
    > >> '
    > >> Range("V7").Select
    > >> Sheets("Entry").Range("A8:N31").AdvancedFilter Action:=xlFilterCopy,
    > >> _
    > >> CriteriaRange:=Sheets("Entry").Range("L3:L4"),
    > >> CopyToRange:=Range("I19:T29" _
    > >> ), Unique:=False
    > >> ActiveWindow.ScrollColumn = 1
    > >> Range("B8").Select
    > >> End Sub
    > >>
    > >>

    > >
    > >


    --

    Dave Peterson

  5. #5
    documike
    Guest

    Re: Macro Error...I Give Up

    Yes, some of the cells are empty in the B:N range. I have 12 columns with
    the left one (A:9:A31) that has TRUE or FALSE entries that come from Check
    Boxes. L3 is the Column heading Col A (Status), and L4 is ="=TRUE" so it
    only picks the rows that are TRUE. The rows that are FALSE have several
    empty cells and aren't populated.

    I can get most of this to work Ok, but still having the problem of limiting
    the placement of the Copy to a restricted range in the sheet I am copying
    to. Even with copying three or four rows, it wipes out the data in cells
    that are 18 rows below. Of course if I turn Protection on (which I want to
    do) it errors out...so it looks like the solution is something to limit the
    copy placement range. Since the sorted data is 22 rows, is it always going
    to take 22 rows of space on sheet 2 even though it may only be copying a few
    rows? Maybe I'm battling something that's inherent in the Advanced
    Filter???


    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > In my simple testing, it copied all the columns.
    >
    > Any chance that the criteria range specifies rows that are empty in B:N???
    >
    > (In case it isn't that, what did you have in L3:L4 of Entry--for testing
    > purposes.)
    >
    >
    >
    > documike wrote:
    >>
    >> Yes, but it then only copies the 1st column to the destination...I have
    >> 12
    >> columns
    >>
    >> "Tom Ogilvy" <[email protected]> wrote in message
    >> news:uQ%[email protected]...
    >> > Just designate the upper left corner of the destination range:
    >> >
    >> > Sub CopyFilterData2()
    >> > '
    >> > ' CopyFilterData2 Macro
    >> > ' Macro recorded 1/1/2005
    >> > '
    >> >
    >> > '
    >> > Range("V7").Select
    >> > Sheets("Entry").Range("A8:N31").AdvancedFilter _
    >> > Action:=xlFilterCopy, _
    >> > CriteriaRange:=Sheets("Entry").Range("L3:L4"), _
    >> > CopyToRange:=Range("I19"), _
    >> > Unique:=False
    >> > Range("B8").Select
    >> > End Sub
    >> >
    >> > This worked for me.
    >> >
    >> > --
    >> > Regards,
    >> > Tom Ogilvy
    >> >
    >> >
    >> > "documike" <[email protected]> wrote in message
    >> > news:2vBBd.55963$k25.37798@attbi_s53...
    >> >> Happy New Year everyone!
    >> >>
    >> >> I am trying to run Data/Advanced Filter & copy filter result to
    >> >> another
    >> >> worksheet. When I manually do it, it works fine, however when I
    >> >> record a
    >> >> macro and run it, I get "Run time error '1004' Extract name has a
    >> >> missing
    >> > or
    >> >> illegal field name.
    >> >> Any ideas? Here's the macro:
    >> >>
    >> >> Sub CopyFilterData2()
    >> >> '
    >> >> ' CopyFilterData2 Macro
    >> >> ' Macro recorded 1/1/2005
    >> >> '
    >> >>
    >> >> '
    >> >> Range("V7").Select
    >> >> Sheets("Entry").Range("A8:N31").AdvancedFilter
    >> >> Action:=xlFilterCopy,
    >> >> _
    >> >> CriteriaRange:=Sheets("Entry").Range("L3:L4"),
    >> >> CopyToRange:=Range("I19:T29" _
    >> >> ), Unique:=False
    >> >> ActiveWindow.ScrollColumn = 1
    >> >> Range("B8").Select
    >> >> End Sub
    >> >>
    >> >>
    >> >
    >> >

    >
    > --
    >
    > Dave Peterson




  6. #6
    Tom Ogilvy
    Guest

    Re: Macro Error...I Give Up

    It only would copy one column if you had a column heading in I19 like you do
    in L3. If not, it coies all columns of the selected rows.

    It does wipe out any data below I19 in my experience (even beyond 22 rows).
    One easy solution is to use Advanced filter to copy the selected data to a
    separate location on sheet Entry, then copy it to cell I19

    Sub CopyFilterData2()
    Range("V7").Select
    Sheets("Entry").Range("A8:N31").AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Entry").Range("L3:L4"), _
    CopyToRange:=sheets("Entry").Range("Z1"), _
    Unique:=False
    Sheets("Entry").Range("Z1').CurrentRegion.Copy _
    Destination:=Range("I19")
    Sheets("Entry").Range("Z1").CurrentRegion.ClearContents
    Range("B8").Select
    End Sub

    --
    Regards,
    Tom Ogilvy




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1