filter macro

[SteveDB1]

Howdie all.
I have a filter macro that Don Guillett made, and I modified to meet my need.
It's worked perfectly up until now.....
I use it daily so it's not something I want to do without.
My problem is that instead of it setting my destination page filter to the
check list at the bottom of the filter drop down, it selects the text filter.
It's never happened before now. Part of me is wondering if it has to do with
the list of sheet names in my Case statement (Has that list grown too long? I
hope not, because the last few names are because I found that some workbooks
have two common Sum names, and require a numeric designation with the Sum
name).

What would cause a response like I've described above?
My code is below.



Sub FilterA()

Dim wks As Worksheet


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

For Each wks In ActiveWorkbook.Worksheets

Select Case wks.Name

Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum ", "Summary ",
"SUMMARY", "SUMMARY ", "SUM79", "SUM189", " SUM79", "SUM189",
"SUM79 ", "SUM189 "
With wks
Sheets(wks.Name).range("A8:F8").AutoFilter field:=1,

Criteria1:=mv '"12345"

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

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

Next wks


End Sub

 

[Don Guillett]

Only looking at the case statement, I don't see how I would have suggested
that.
Sub testcase()
For Each ws In Worksheets
If UCase(Left(ws.Name, 3)) = "SUM" Then
MsgBox ws.Name
End If
Next ws
End Sub

 

[SteveDB1]

Hi Don,
Thank you for your response.
you're correct in stating that you didn't do it like I show. As I said-- I
made some modifications.
what you'd originally given to me was as follows:

---------------------------------------------------------------
Dim wks As Worksheet

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

For Each wks In ActiveWorkbook.Worksheets

Select Case wks.Name

Case "Sum", "Summary"
With wks
Sheets(wks.Name).range("A8:F8").AutoFilter field:=1,
Criteria1:=mv '"12345"

End With
End Select

Next wks
'or some very minor variation from the above. in fact, I changed the range.
---------------------------------------------------------------

As I looked through what you'd done, and compared it to the one I'd
recorded, I began making some dramatic modifications to match what I needed.
And up until this morning, I didn't have any troubles with it at all.

While I see the UCase statement is indeed better, there are too many
variations of the name Sum for the Left to work.

I.e., sadly, there are cases in which there is a space before the word Sum,
and I never know when it'll occur. In fact, with the way worksheet names are
shown, I've had a multitude of occurrences where I had errors thrown because
it couldn't find the sum sheet, and I was looking right at it-- with other
macros. After a bit of digging around I realized that there was a space in
front of, or after the sheet name. This was why I'd used so many variations
of the Case statement choices. Thus, since we have so many files, it'd take
months to go through, and rename all of the sum sheets to a common name to
resolve that issue.

So, back to my question....
How would I prevent the text filter from being set?
The filter usually just sets the general filter list in the drop down, once
I set my choice from my source sheet.

Again, thank you.
Best.

 

[SteveDB1]

My mistake Don,
It was Tom Hutchins that provided that filter macro to me.
I'd posted it back on October 15th. Barb Reinhardt provided another version
which I never used.
I'd titled my post- variant sheet name to filter.
So, please accept my apology for confusing it.

Best.

 

[SteveDB1]

As a further addendum, I've found that the text filter selection error only
occurs
when I select blanks in the filter from my source page.
It's as though the filter no longer exists or is only filtering off a single
filter selection, yet when I look at the code, nothing's changed from
yesterday when it worked fine.

Any helps would be appreciated.