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