Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 5th, 2009, 08:32 PM
Authorized User
Join Date: Sep 2007
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Autofilter, multiselect, 2007

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.

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Enter Criteria Above Header To apply Autofilter stroggo Excel VBA 2 April 16th, 2008 03:35 AM
Remove Autofilter yogeshyl Excel VBA 4 June 11th, 2007 06:59 AM
AutoFilter 5 columns at once Pindacko Excel VBA 0 April 26th, 2006 05:33 AM
Autofilter taking too much of time raaghu15 Excel VBA 2 March 4th, 2005 05:43 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.