LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #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
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Error Msg - Object variable or With Blockvariable not set Mike R. Excel Programming 2 November 13th 08 06:55 PM
Object Variable Not Set Error on Selection object Jean Excel Worksheet Functions 3 July 24th 06 06:45 PM
Run-time error '91': "Object variable or With block variable not set Mike[_92_] Excel Programming 2 December 30th 04 10:59 AM
Cells.Find error Object variable or With block variable not set Peter[_21_] Excel Programming 2 May 8th 04 02:15 PM
Pivot Table - Object variable or with block variable not set? George Nicholson[_2_] Excel Programming 1 April 16th 04 09:12 PM


All times are GMT +1. The time now is 09:47 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"