View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
T2B T2B is offline
external usenet poster
 
Posts: 3
Default Filtering Worksheets by Same Criteria - Object variable or with blockvariable not set

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