Macro Error...I Give Up

[documike]

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

 

[Tom Ogilvy]

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.

 

[documike]

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

 

[Dave Peterson]

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]

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???

 

[Tom Ogilvy]

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