Autofilter macro

martymartin

New Member
Joined
Jul 13, 2007
Messages
6
Column A has a list of data (A,B,C, etc), I have created vb macro to autofilter on sheet DATA from worksheet A

Sub Extract_Data_A()
Sheets("DATA").Select
Range("A1").Select
Range("A2:S3000").Select
'Apply Autofilter
Selection.AutoFilter
FilterCriteria = "A"
'NOTE - this filter is on column N (field:=14), to change
'to a different column you need to change the field number
Selection.AutoFilter field:=17, Criteria1:=FilterCriteria
Range("A1").Select
Application.ScreenUpdating = True
End Sub

This works and I can assign this to a button, however, I would like the macro to take the value from Worksheet A of A1 (A) and use it as the filter so I do not have to create a macro for each one individually.

Any ideas?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Just change your criteria line like this:
Code:
FilterCriteria = Sheets("A").Range("A1").Value

Actually, your code can be shortened by removing all the Selecting:
Code:
Sub Extract_Data_A()
Sheets("DATA").Select
    FilterCriteria = Sheets("A").Range("A1").Value
    Range("A2:S3000").AutoFilter Field:=17, Criteria1:=FilterCriteria
Application.ScreenUpdating = True
End Sub
 

martymartin

New Member
Joined
Jul 13, 2007
Messages
6
That makes sense, however, would I have to create a macro for each cell of filtercriteria. (ie.. A1 contains A, A2 contains B, A3 contains C, etc..)

I would need to have a macro for each cell reference (A1, A2, A3, etc..), that seems like many macros? The cell references are actually numbers and they increment by 32 (1, 33, 65, etc..)
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
No need to create a macro for each cell.
This code takes the value from the active cell and uses it as crtiria in your autofilter.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim VRange As Range, cell As Range
    Set VRange = Range("A1:A15")
    For Each cell In Target
        If Target = "" Then Exit Sub
        If Union(cell, VRange).Address = VRange.Address Then
            FilterCriteria = ActiveCell.Value 'Sheets("A").Range("A1").Value
            Sheets("DATA").Range("A2:S3000").AutoFilter Field:=17, Criteria1:=FilterCriteria
        End If
    Next cell
End Sub
This is Event code. Put code in the WorkSheet Module.
Right click sheet tab for sheet "A".
Click "View Code"
Paste the code into the panel that opens. ("WorkSheet" Module)
Alt-Q to quit the VBA Editor.

Now when you click one of the cells, A1 through A15 in the worksheet named "A", that cell value will be used as your autofilter criteria for the worksheet named "Data". Adjust the range of A1:A15 to suit.
 

martymartin

New Member
Joined
Jul 13, 2007
Messages
6
That is perfect, I extended the range from A15 to A2100, however, now when I try to select a range of cells anywhere not in column A, I get debug error on this line

If Target = "" Then Exit Sub
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Add this line of code before the line you get your error on.
Code:
        If Target.Count > 1 Then Exit Sub
This will exit if you select more than one cell.
 

martymartin

New Member
Joined
Jul 13, 2007
Messages
6
Here is a bit more of a challenge (to me anyway)...

In column B, I have data that should autofilter the table to column 15 and in column C, I have data that should autofilter the table to column 17.

How do I make that work with this code provided by Datsmart?
 

Forum statistics

Threads
1,181,375
Messages
5,929,585
Members
436,681
Latest member
natalie123vba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top