Select an Autofilter field based on cell contents instead of a field number

<NOOB ALERT - GO EASY ON ME>

Hi there,

I am trying to create a macro that applies an Autofilter. The filter should select a field based on cell contents in a different worksheet.

From what I can tell, Autofilter Fields can only be specified as numbers indicating the column number of that field (e.g. "AutoFilter Field:=2"). I want to do something a bit different... My code needs to reference a cell on a different worksheet, and that cell should tell the autofilter which field to filter on.

In the attached workbook, there are two tabs. The "DATA" tab contains the data that requires filtering. The "INPUT" tab is where the user will specify how the filter should be applied. On the INPUT tab you can see that I have a drop-down list in cell C5 which allows the user to select a weekday (in this case "Monday"). This needs to tell the Autofilter to filter on the "Monday" field of the DATA tab and select any record with a "1" in that field. If I select "Tuesday" on the INPUT tab, I want the code to filter on the "Tuesday" field of the DATA tab and select any record with a "1" in that field. The same would apply for any other weekday in the drop-down list.

Thanks in advance.
Ron

Re: Select an Autofilter field based on cell contents instead of a field number

Thank you very much, this worked perfectly!

Quote from royUK;774326

This will find the column then filter the data on that column

Code

Option Explicit


Sub FilterDay()


    Dim iCol As Integer


    If Sheets("Input").Range("c5") = "" Then
        MsgBox "Please select a day", vbCritical, "Input required"
        Sheets("Input").Range("c5").Select
        Exit Sub
    End If


    With Sheets("Data")
        If Not .AutoFilterMode Then .Range("A2").AutoFilter
        iCol = .Range("A2:J2").Find(What:=Sheets("Input").Range("c5").Text).Column
        .Range("$A$2").CurrentRegion.AutoFilter Field:=iCol, Criteria1:="1"
    End With
End Sub

Display More

Re: Select an Autofilter field based on cell contents instead of a field number

Hi there, thank you for providing this insight.

I didn't try this solution because I wanted to trigger the data pull based on a discrete action from the user (e.g. clicking a button) instead of auto-filtering as data was entered. However this is still a slick piece of code and I think I can find a way to use it someday!

Thank you

Quote from sktneer;774327

I think you should place the following code on Input Sheet Module so each time you change the selection, the data on Data sheet will be autofiltered as per the selection made in C5.
To do that, right click on the Input Sheet Tab --> View Code --> Paste the code given below into the opened code window --> Close the VB Editor --> Save your workbook as Macro-Enabled Workbook.
In the attached, I have already placed the code on Input Sheet Module so you don't need to repeat the above step in the attached.

Remember to input All in A1 on Drop Down List Data Sheet, so that you can also select All in the drop down and you will be able to see all the data on the Data sheet.

For details, refer to the attached.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim ws As Worksheet
Dim Col As Long


If Target.Address(0, 0) = "C5" Then
   If Target <> "" Then
      Set ws = Sheets("Data")
      If Target = "All" Then
         ws.AutoFilterMode = 0
         Exit Sub
      End If
      Col = ws.Range("A2:J2").Find(Target.Value).Column
      ws.AutoFilterMode = 0
      With ws.Rows(2)
         .AutoFilter Field:=Col, Criteria1:="1"
      End With
   End If
End If
End Sub

Display More