Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
autofilter macro
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
autofilter with macro | Excel Discussion (Misc queries) | |||
Macro, AutoFilter... | Excel Discussion (Misc queries) | |||
Autofilter Macro? | Excel Discussion (Misc queries) | |||
Macro to Autofilter | Excel Programming | |||
autofilter using macro | Excel Programming |