Autofilter, multiselect, 2007

SteveB · #1 (**permalink**) March 5th, 2009, 08:32 PM

Howdie all.

I'm using a macro that links two worksheets by an autofilter.
I.e., I have filters set on each worksheet. I then select the filter elements I want on one sheet, and activate my macro, and it sets the filter on a second sheet, to match my chosen elements from the first sheet. I then have a series of subtotal, and IF equations set to test of the values from each sheet match or not.

There are two elements that I've "linked" by this method, and they are the user name, and a 5 digit code.
In the filter dropdown, there are the standard- alpha_rev/fwd sort, color sort, color filter, text filter, and the multi-select list.
In the multiselect list, I'll have my data, and at the bottom, I always have a "Blanks" option as well.

Recently, the 5 digit filter has set my "Blanks" option on the second sheet to a text filter. When I go to check what the text is being set, it shows a word that's not in my chosen list, or even in the same column for my criteria.

I.e., up until around the beginning of the year it worked excellently, and did exactly what I wanted.

At this point, I'm just looking to get the text filter to stop being set, and ensure that the multiselect is chosen.

I've looked in the Excel VBA help file, on MSDN library, posted numerous times on the MS newsgroups, looked on Google, Contextures.com, etc..... I'm unable to find anything that explains what's taking place, or how to make it do what it did before.

Oh, I tried using the Operator for the autofilter, and the xlvalues did not work.
I then read a bit on multiselect, and thought I could try that..... of course that did not work either.

here is my code below.

Code:

 
Sub FilterA()
Dim wks As Worksheet
 
mv = range("f2").End(xlDown).value  
 
' this sets the criteria for the ChgAppl#.
 
mv1 = range("a2").End(xlDown).value 
 
'this is my add-on to set a second criteria filter- Name of owner.
 
For Each wks In ActiveWorkbook.Worksheets
    If LCase(Left(wks.Name, 4)) Like "sum" Then
 
 
            With wks
 
               Sheets(wks.Name).range("A8:F8").AutoFilter field:=1, Criteria1:=mv 
', Operator:=MultiSelect  'this takes in to acct the chg appl# for a filter.
 
 
                Sheets(wks.Name).range("A8:F8").AutoFilter field:=4, Criteria1:=mv1   
'this takes in to account the owner name for a filter.
            End With
    End If
 
Next wks
 
End Sub

Need to download code?

Navigation