0

I've been struggling with this error for about an hour and a half and haven't been able to find an answer. Perhaps you could help me out.

I have a short sub that locates a column labeled "Filter" in a sheet. It turns on an autofilter for that column and hides it. 

Sub Test1()

    Call FilterHide(Worksheets("Integration"))
    Call FilterHide(Worksheets("Integration Matrix"))

End Sub

Sub FilterHide(ByVal target As Worksheet)
'
'Searches for filter column, then autofilters selection to show only "Show"

    Dim clm As Integer

    If IsError(Application.Match("Filter", target.Range("1:1"), 0)) Then
        Exit Sub
    End If

    clm = Application.Match("Filter", target.Range("1:1"), 0)
    target.Range(Columns(clm), Columns(clm)).AutoFilter Field:=1, Criteria1:="Show"
    target.Range(Columns(clm), Columns(clm)).EntireColumn.Hidden = True

End Sub

'FilterHide()' works fine when I call it once. However, when I attempt to call it twice from another sub with different sheets, it breaks (e.g. 'Test1()' above). I get "run-time error 1004: Method 'Range' of object '_Worsksheet' failed." The debugger then points me to the following line 

target.Range(Columns(clm), Columns(clm)).AutoFilter Field:=1, Criteria1:="Show"

I can run FilterHide() successfully for all sheets if I do it one at a time. It's only when I call it multiple times that I get errors. My ultimate goal is to create a Worksheet_Deactivate() event that runs FilterHide() for about 7 different worksheets.

Community
  • 1
  • 1
user2880149
  • 3
  • 1

1 Answers1

1
target.Range(Columns(clm), Columns(clm)).AutoFilter

Here the unqualified Columns(clm) will refer to the ActiveSheet, not target.

You need:

target.Range(target.Columns(clm), target.Columns(clm)).AutoFilter

but this reduces to 

target.Columns(clm).AutoFilter
Tim Williams
  • 154,628
  • 8
  • 97
  • 125