autofilter on a not-active sheet

[Peter]

Hello everyone,

I screwed something up that worked just fine earlier on but now it no longer
does.

This line of code still works from the immediate window, even though the
range is on a not-active sheet:

Range("HeaderWeek").AutoFilter Field:=3, Criteria1:=4

Criteria1 in my original code is actually a string-variable, which is also
named HeaderWeek. Since the original code failed, I hard-coded Criteria1 to
"4" (or just 4, doesn't matter from the immediate window where it does
work), but still it doesn't work in the original code. The error I get is:

Method 'Range' of object '_Worksheet' failed.

BTW This code is located in a Worksheet_Change event, if that has any
bearing on this situation.

Greetings,
Peter

 

[Norman Jones]

Hi Peter,

Range("HeaderWeek").AutoFilter Field:=3, Criteria1:=4

Try Qualifying the range:

Me.Range("HeaderWeek").AutoFilter Field:=3, Criteria1:=4

 

[Dave Peterson]

If it's on a different worksheet than the one that owns the code, I bet you did
something like this first:

worksheets("othersheet").select
Range("HeaderWeek").AutoFilter Field:=3, Criteria1:=4

That unqualified range object refers to the worksheet that owns the code when
you're in that worksheet's code module.

You could use:

worksheets("othersheet").select
worksheets("Othersheet").Range("HeaderWeek").AutoFilter Field:=3, Criteria1:=4

or even drop the .select and just work on the range directly:

worksheets("othersheet").Range("HeaderWeek").AutoFilter Field:=3, Criteria1:=4