autofilter on a not-active sheet
 

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

autofilter on a not-active sheet
 

Hi Peter,

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

Try Qualifying the range:

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

---
Regards,
Norman



"Peter" wrote in message
...
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

autofilter on a not-active sheet
 

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").AutoF ilter Field:=3, Criteria1:=4

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

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



Peter wrote:

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

--

Dave Peterson