variant sheet name to filter

[SteveDB1]

Morning all.
A while back Don Guillett provided me with a filter routine that has been
working really well-- with a single exception.
My sheet names that I'm calling to don't always remain constant.

I.e., sometimes it'll be Sum, with others it'll be Summary, SUMMARY, etc.....
I'd like to get the routine to accept all variations of Sum, without me
having to rewrite the elements to take into account the different name each
time I run the routine in a new workbook.

Here's the code.
------------------------------------------------------------------------
Sub FilterA()

Dim wks As Worksheet
'I added this because I wanted to get a name variable
'for a worksheet

mv = Range("f3").End(xlDown).value ' this sets the criteria.

Select Case wks.Name
'I looked at another macro that I have and see that it used Select to
account for
'all name variables.
Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum ", "Summary ",
"SUMMARY", "SUMMARY "
'this is the code that I pulled from the other macro accounting for the name
'variables.

With wks
Sheets(wks.Name).Range("A8:F8").AutoFilter field:=1, Criteria1:=mv
'the original version that Don G. provided gave
'Sheets("Sum")....... It's here that I need to do the variable name.


mv1 = Range("a3").End(xlDown).value 'this is my add-on to set a second
criteria filter- Name of owner.

'And here too to account for variable sheet name.
Sheets(wks.Name).Range("A8:F8").AutoFilter field:=3, Criteria1:=mv1 'this
takes in to account the owner name for a filter.

End With
End Select

End Sub

------------------------------------------------------------
I had thought this would work, but it throws a 91 error-- object variable or
with block not set.

What do I need to do to set the object variable? I'm assuming that is my
issue.

Thank you.
Best,
SteveB.
I'm running Excel 2007.

 

[Tom Hutchins]

wks is an object variable (a worksheet). You have to assign it to a specific
worksheet using the SET command, or you can use it as follows to represent
each worksheet in turn within a loop:

Sub FilterA()
Dim wks As Worksheet
mv = Range("f3").End(xlDown).Value ' this sets the criteria.
For Each wks In Worksheets
wks.Activate
Select Case wks.Name Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum
", "Summary ", "SUMMARY", "SUMMARY "
With wks
.Range("A8:F8").AutoFilter field:=1, Criteria1:=mv
mv1 = Range("a3").End(xlDown).Value
.Range("A8:F8").AutoFilter field:=3, Criteria1:=mv1
End With
Case Else
'do nothing
End Select
Next wks
End Sub

Hope this helps,

Hutch

 

[Barb Reinhardt]

I think I'd do something like this

if lcase(ws.name) like "sum*" then
'Do what you want to do
end if

 

[SteveDB1]

Thanks Tom,
The for loop was in fact in the code I pulled the select code from, but I
used it to run through all the worksheets in a workbook, so I thought it was
superfluous, and didn't draw it out.
I just tried it and it works exactly as I wanted.
Best.

 

[SteveDB1]

Barb,
So, I'm guessing then that I'd wrap the If statement around the code I'm
using, and the (sum*) will read any variation of the word in the
worksheets.name?

if lcase(ws.name) like "sum*" then
'Do what you want to do
end if

 

[Barb Reinhardt]

Correct. It will look for anything beginning with SUM (or Sum or sum or Sum
... .you get the picture.

 

[SteveDB1]

cool.
thanks.
Best.

Correct. It will look for anything beginning with SUM (or Sum or sum or Sum
.. .you get the picture.