Advanced filter xlFilterCopy Question

[jrobin5881]

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

 

[PHV]

You may have to use such syntax;
CopyToRange:=Worksheets("sheet name").Range("ext")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jrobin5881]

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

 

[Gavona]

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

 

[SkipVought]

You must COPY to the ActiveSheet...

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,

_[red][/red]

 

[SkipVought]

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,

_[red][/red]

 

[Gavona]

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:

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

 

[PHV]

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jrobin5881]

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

 

[SkipVought]

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,

_[red][/red]

 

[Gavona]

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

 

[SkipVought]

Gavin,

Naturally, you were correct. Sorry

The error only occurs on a sheet.

Skip,

_[red][/red]