Problem in using Advanced Filter

S

Shilps

Hi,
I am trying to use Advanced Filter. I have this code

Range("A5:J500").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Sheets( _
"Sheet3").Range("A6:B8"), CopyToRange:=Range("A136"), Unique:=False
Now the problem with the macro is that even when criteria is changing in
Range "A6:B8", it is always giving the same result that it gave when it was
run for the first time.

What is the reason for this error? Please help.
TIA
Shilps
 
T

Tom Ogilvy

I would suspect that your criteria is either not specified properly or it at
leasts it does not produce what you expect.

for example

Name
Dog

as a criteria would also return records where the value in the Name column
was doghouse - you might expect only to get those with dog.
 
T

tina

Shilps said:
Hi,
I am trying to use Advanced Filter. I have this code

Range("A5:J500").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Sheets( _
"Sheet3").Range("A6:B8"), CopyToRange:=Range("A136"), Unique:=False
Now the problem with the macro is that even when criteria is changing in
Range "A6:B8", it is always giving the same result that it gave when it was
run for the first time.

What is the reason for this error? Please help.
TIA
Shilps
Click to expand...

Try CopyToRange:=Range("A136:J136") don't know why but seeems to work for me

Tina
 
P

...Patrick

Go to CriteriaRange:=Sheets("Sheet3").Range("A6:B8"),

with F5 and look if this area is not changed by A6:B9 ???
 
D

Debra Dalgleish

If your criteria range contains blank rows you'll get all the rows from
the table returned when you run the Advanced filter.

And you could qualify your range references, to ensure that the correct
ranges are being used. For example:

Dim wsA As Worksheet
Dim wsB As Worksheet
Set wsA = Sheets("Sheet1")
Set wsB = Sheets("Sheet3")
wsA.Range("A5:J42").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=wsB.Range("A6:B8"), _
CopyToRange:=wsA.Range("A136"), Unique:=False
 
Top