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


T

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
 
Ad

Advertisements

G

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
 
P

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
 
G

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
 
G

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
 
Ad

Advertisements

G

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

error trying to sort data 4
filter macro 3
variant criteria name to filter 12
filter macro 4
new worksheet name 5
variant sheet name to filter 6
Tom Hutchins macro- Filter 1
autofilter macro 1

Top