Tom Hutchins macro- Filter

S

SteveDB1

Morning all.
I'm working with an autofilter macro that I received from here from Tom
Hutchins last year.
For the most part, it's been working really well, and has helped expedite my
workload-- alot!!!! So, Tom, if you're reading this-- it's been really
helpful, and I just wanted to say so, as well as ask another question about
solving a problem that's arisen more recently.

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. I.e., it appears to be setting a text filter,
instead of selecting out of a drop down block of choices as a normal
filtering operation would perform.
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 by Tom H.

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

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 page

Column A to match Column F on source page

Column B to match "Claim#" from source page

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.
 
S

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......
 

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

Top