Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Advanced filter xlFilterCopy Question

Status
Not open for further replies.

jrobin5881

Technical User
Mar 10, 2004
228
US
I have a dataset that I am filtering on a combo box. I read many of the posts here concerning Advanced filtering and there was a thread (which I lost the name and number of)that walked you through setting up a filter using VBA and it worked fine. The thread went on to say that you could copy the filtered data and paste it into another sheet which is what I want to do with a little of the code tweaked. The DropDown_Change sub worked fine(see below) but I'm getting an error message when I replace that code with the Extract_Data sub.

The message says"The extract range has a missing or illegal field name" I tried naming a range "ext" (no quotes) in the same sheet & in another sheet to make it work to no avail.

Whats wrong? why did it run fine for the first code but not the second?

PS: Code is written in a module and is assigned as a macro to a FORM combo box on the database page.


Sub DropDown_Change()
crit = [crit_type].Value
Range("dataset").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=crit, _
Unique:=False
Extract_Data
End Sub


Sub Extract_Data()
Range("dataset").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=crit, _
CopyToRange:=Range("ext"), Unique:=False
End Sub
 
You may have to use such syntax;
CopyToRange:=Worksheets("sheet name").Range("ext")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
I'm getting this message now: "Application-defined or Object-defined error" My ammended code is below

Sub Extract_Data()

Range("dataset").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=crit, _
CopyToRange:=Worksheets("Sheet3").Range("ext"), _
Unique:=False
End Sub
 
Unless your ranges are named at sheet level rather than workbook level then you do not need to be explicit about the sheet.

I suspect that Crit is not a range.



Gavin
 



You must COPY to the ActiveSheet...
Code:
Sub Extract_Data()
    [b]Worksheets("Sheet3").activate
    Sheets("[i]Whatever[/i]").[/b]Range("dataset").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=crit, _
    CopyToRange:=Worksheets("Sheet3").Range("ext"), _
    Unique:=False
End Sub

Skip,

[glasses] [red][/red]
[tongue]
 



BTW, I am a BIG advocate for AVOIDING the Activate and Select methods.

However, this is one of the very few examples where this method is required.

Skip,

[glasses] [red][/red]
[tongue]
 
Actually Skip, if you use named ranges and VBA you do not need to copy to the active sheet.

Indeed just checking 'cos you are so rarely wrong I discover that if the named range used for the ExtractTo is defined to refer to another workbook e.g. =[Book2]Sheet1!$A$5:$F$5 then in Excel 2003 you can filter to another workbook. The only requirement is that the active workbook must contain the names.

So I can I run this code with book2 active:
Code:
Sub TEST()
Range("alldata").AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Range("Criteria"), _
    CopyToRange:=Range("output"), _
    Unique:=False
End Sub
so long as the following ranges are defined in book2:
alldata =[Book1]Sheet2!$A$3:$F$10
criteria =[Book1]Sheet1!$A$3:$A$4
output =Sheet1!$A$5:$F$5

Or I can run the macro while Book1 is active so long as the following ranges are defined in Book1:
Alldata =Sheet2!$A$3:$F$10
CRITERIA =Sheet1!$A$3:$A$4
OUTPUT =[Book2]Sheet1!$A$5:$F$5

Assuming of course appropriate content in the named ranges.

Learn something every day!



Gavin
 
jrobin, have you tried to replace this:
CriteriaRange:=crit
with this ?
CriteriaRange:=Range("crit")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Thank you all - I had to put this aside for a moment but will look at it again in a little while. I'll let you know how it goes
 




Gavin,

I used a Named Range, and got the error message. The filter only worked if the ActiveSheet was the destination sheet. Not the way I would normally do it, as I almost ALWAYS use named ranges.

Actually, the way I would have accomplished the task, would have been to do a Select Distinct query.

Skip,

[glasses] [red][/red]
[tongue]
 
Skip,
filter only worked if the ActiveSheet was the destination sheet
All I can say is that for me, with Excel2003 destination does not have to be on the active sheet PROVIDED that you apply the Advanced Filter using VBA. I am 99% sure this was true in 2000 as well.
Regards,


Gavin
 




Gavin,

Naturally, you were correct. Sorry [blush]

The error only occurs on a sheet.

Skip,

[glasses] [red][/red]
[tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top