Filtering Worksheets by Same Criteria - Object variable or with blockvariable not set

[T2B]

Filtering across sheets with same same filter headers I am using this
but occasionally will get the error listed below. Other times it
works fine. Please help if you can see the error in my ways.

Sub filterSHEETS(ReturnArray() As String, iField As Integer)

Dim astrItems() As String

Dim wks As Worksheet

Dim strSheetName As String

For Each wks In ActiveWorkbook.Worksheets

With wks

strSheetName = wks.Name

Select Case strSheetName

Case "Amenity", "Benchmark", "Rate", "Volume",
"Negotiation Tool Report"

Sheets(strSheetName).Select

If .FilterMode Then
.ShowAllData
End If

'this code will sometimes break here with this error code

'Run-time error '91':
'Object variable or with block variable not set

With ActiveWorkbook.ActiveSheet.AutoFilter.Range
.AutoFilter Field:=iField,
Criteria1:=ReturnArray, Operator:=xlFilterValues
End With

End Select

End With

Next wks

End Sub

 

[GS]

T2B explained on 7/13/2011 :

Filtering across sheets with same same filter headers I am using this
but occasionally will get the error listed below. Other times it
works fine. Please help if you can see the error in my ways.

It usually is necessary to do '.AutoFilter' followed by:
'.AutoFilter Field:=...' on the specified range so errors don't
occur.

Not sure why you're using 'AutoFilter.Range' here. IOW, specify the
range to filter something like this...

Sub filterSHEETS(ReturnArray() As String, iField As Integer, _
RangeToFilter As Range)
Dim astrItems() As String '//what purpose does this serve?

Dim wks As Worksheet
'Dim strSheetName As String '//not really needed

For Each wks In ActiveWorkbook.Worksheets
With wks
Select Case .Name
Case "Amenity", "Benchmark", "Rate", "Volume", _
"Negotiation Tool Report"
.Select
With .Range(RangeToFilter)
.AutoFilter
.AutoFilter Field:=iField, _
Criteria1:=ReturnArray, _
Operator:=xlFilterValues
End With '.RangeToFilter
End Select 'Case .Name
End With 'wks
Next wks
End Sub

 

[Paul Robinson]

Hi
There is no else Case in your Select Case, so it could be that. Are
there sheet names which are not in your Case list?
regards
Paul

 

[GS]

Paul raises a good point! I wouldn't ordinarily use Select Case in this
case and so I have revised the code to eliminate it as follows...

Sub filterSHEETS(ReturnArray() As String, iField As Integer, _
RangeToFilter As Range)
Dim astrItems() As String '//what purpose does this serve?

Dim wks As Worksheet
'Dim strSheetName As String '//not really needed

Const sSheetNames As String = "Amenity,Benchmark,Rate,Volume," _
& "Negotiation Tool Report"

For Each wks In ActiveWorkbook.Worksheets

With wks
If InStr(.Name) > 0 Then
.Select
With .Range(RangeToFilter)
.AutoFilter
.AutoFilter Field:=iField, _
Criteria1:=ReturnArray, _
Operator:=xlFilterValues
End With '.Range(RangeToFilter)
End If ' InStr(.Name)
End With 'wks
Next wks
End Sub

 

[GS]

Oops! typo corrections...

Sub filterSHEETS(ReturnArray() As String, iField As Integer, _
RangeToFilter As Range)
Dim astrItems() As String '//what purpose does this serve?

Dim wks As Worksheet
'Dim strSheetName As String '//not really needed
Const sSheetNames As String = "Amenity,Benchmark,Rate,Volume," _
& "Negotiation Tool Report"

For Each wks In ActiveWorkbook.Worksheets
With wks
If InStr(sSheetNames, .Name) > 0 Then
.Select
With .RangeToFilter
.AutoFilter
.AutoFilter Field:=iField, _
Criteria1:=ReturnArray, _
Operator:=xlFilterValues
End With '.RangeToFilter
End If ' InStr(.Name)
End With 'wks
Next wks
End Sub

 

[GS]

And, finally, a tested version...

Sub filterSHEETS(ReturnArray() As String, iField As Integer, _
RangeToFilter As String)
Dim astrItems() As String '//what purpose does this serve?

Dim wks As Worksheet
Const sSheetNames As String = "Amenity,Benchmark,Rate,Volume," _
& "Negotiation Tool Report"

For Each wks In ActiveWorkbook.Worksheets
With wks
If InStr(sSheetNames, .Name) > 0 Then
.Select
With .Range(RangeToFilter)
.AutoFilter
.AutoFilter Field:=iField, _
Criteria1:=ReturnArray
End With '.Range(RangeToFilter)
End If ' InStr(sSheetNames, .Name) > 0
End With 'wks
Next wks
End Sub