Autofilter macro

[martymartin]

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?

 

[Datsmart]

Just change your criteria line like this:

FilterCriteria = Sheets("A").Range("A1").Value

Actually, your code can be shortened by removing all the Selecting:

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]

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]

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.

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]

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]

Add this line of code before the line you get your error on.

        If Target.Count > 1 Then Exit Sub

This will exit if you select more than one cell.

 

[martymartin]

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?