autofilter macro

[SteveDB1]

Morning all.
I'm working with an autofilter macro that I received from here last year.
For the most part, it's been working really well, and has helped expedite my
workload-- alot!!!!

I've come to a place with it that it's not working as hoped.
The problem is that when I select my source filter elements it selects other
elements that I've not set, so on my destination page, it blanks out the
groups that I am looking for.
As I've made my modifications, I've tried paying close attention to the
fields, and criteria to make sure that it's actually picking my desired
elements... In examining the elements I've set for, for reasons I cannot
identify, it sets one other element that I did not set up. I.e., I've made
code for 3 elements from my source worksheet, and on the destination
worksheet, it sets four. And I have made sure that it's selecting the right
columns-- based on my criteria.


My code is as follows-- it's a modification of the original source provided
last year.

--------------------------------

Dim wks As Worksheet
Dim fname As String

fname = range("a1")

fname = Right(fname, 3)


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

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

mv2 = range("a1").End(xlUp).value 'this is meant to account for claim #.
mv2 = fname

For Each wks In ActiveWorkbook.Worksheets

Select Case wks.Name

Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum ", "Summary ",
"SUMMARY", "SUMMARY "
With wks
Sheets(wks.Name).range("A14:G14").AutoFilter field:=1, Criteria1:=mv
'this takes in to acct the chg appl# for a filter.

Sheets(wks.Name).range("A14:G14").AutoFilter field:=4, Criteria1:=mv1
'this takes in to account the owner name for a filter.

Sheets(wks.Name).range("A14:G14").AutoFilter field:=2, Criteria1:=mv2
'this takes in to account the claim #

End With
End Select

Next wks

----------------------------------------
sample data set

Source page

Column A- Owner's name
Column F- 5 digit "change application #"
"Claim#"- which I pull from the first row, and strip off the first 6-10
characters, only leaving the last 3 characters.

destination page = "Sum*"

Column D to match Column A on Source

Column A to match Column F on source

Column B to match "Claim#" from source.

As I look back at the field for mv1, or my second autofilter, on the
destination page, it's setting a text filter, instead of the filter that I
"thought" I chose.

Why is the text filter being set, and how can I prevent that?

It was never a problem until recently. Or, perhaps I should say that if it
had always set the text filter, the data that I sought, always matched the
criteria.

now there appears to be data sets that I'm using that don't match the text
filter, and I don't understand why.

Thank you for your helps.

 

[SteveDB1]

For what it's worth, I finally figured out what my filter problem was.
The issue started when I'd created a test utility for verification on my
compound ownerships.
I'd placed a series of equations down about 20 rows below the end of my
data. As a title for one of my columns I'd given it a name. That column
matched my primary filtering criteria, and was being caught by the filter to
make it a text filter instead of a numeric selection filter.
So, I found that by moving my test utility over one column to the right--
outside the domain of my filter's criteria column it resolved my issue, and
I'm now back in business with my filter.
Go figure that things could get so sticky......