Sub FilterLine()
'This macro will filter data within a table based on the value input in a cell
Const wsName As String = "Setup Data" ' change this constant (in quotes) to the WORKSHEET name that contains your table
Const tableName As String = "tbl_PL" ' change this constant (in quotes) to the TABLE name that houses the data you want filtered
Const targetColumnName As String = "Prod Line" ' change this constant (in quotes) to the FIELD name (table header) you want to filter on
Const targetFilter As String = ("K2") ' change this constant (in quotes) to the cell location where your filter criteria is located
Const targetFilterSheet As String = "Reports" 'change this constant (in quotes) to the name of the sheet where your filter cell resides
Dim FilterCriteria
Dim ws As Excel.Worksheet
Set ws = ActiveWorkbook.Sheets(wsName)
Dim tbl As Excel.ListObject
Set tbl = ws.ListObjects(tableName)
Dim targetColumn As Excel.ListColumn
Set targetColumn = tbl.ListColumns(targetColumnName)
Dim targetColumnIndex As Long
targetColumnIndex = targetColumn.Range.Column
On Error Resume Next
ActiveSheet.AutoFilterMode = False
On Error GoTo 0
'Get the filter's criteria from the user. If you want the filter to be wildcard based, you can remove the comment mark ' from
'the 3 indented lines below.
FilterCriteria = Worksheets(targetFilterSheet).Range(targetFilter).Value
'If FilterCriteria = "" Then Exit Sub
'FilterCriteria = Replace(FilterCriteria, "*", "")
'FilterCriteria = "*" & FilterCriteria & "*"
'Filter the data based on the user's input
tbl.Range.AutoFilter field:=targetColumnIndex, Criteria1:=FilterCriteria
'Scroll up to the top so user more easily sees the filtered data
ActiveWindow.SmallScroll Down:=-30000
End Sub
Sub FilterProduct()
'This macro will filter data within a table based on the value input in a cell
Const wsName As String = "Setup Data" ' change this constant (in quotes) to the WORKSHEET name that contains your table
Const tableName As String = "tbl_PL" ' change this constant (in quotes) to the TABLE name that houses the data you want filtered
Const targetColumnName As String = "Product" ' change this constant (in quotes) to the FIELD name (table header) you want to filter on
Const targetFilter As String = ("K3") ' change this constant (in quotes) to the cell location where your filter criteria is located
Const targetFilterSheet As String = "Reports" 'change this constant (in quotes) to the name of the sheet where your filter cell resides
Dim FilterCriteria
Dim ws As Excel.Worksheet
Set ws = ActiveWorkbook.Sheets(wsName)
Dim tbl As Excel.ListObject
Set tbl = ws.ListObjects(tableName)
Dim targetColumn As Excel.ListColumn
Set targetColumn = tbl.ListColumns(targetColumnName)
Dim targetColumnIndex As Long
targetColumnIndex = targetColumn.Range.Column
On Error Resume Next
ActiveSheet.AutoFilterMode = False
On Error GoTo 0
'Get the filter's criteria from the user. If you want the filter to be wildcard based, you can remove the comment mark ' from
'the 3 indented lines below.
FilterCriteria = Worksheets(targetFilterSheet).Range(targetFilter).Value
'If FilterCriteria = "" Then Exit Sub
'FilterCriteria = Replace(FilterCriteria, "*", "")
'FilterCriteria = "*" & FilterCriteria & "*"
'Filter the data based on the user's input
tbl.Range.AutoFilter field:=targetColumnIndex, Criteria1:=FilterCriteria
'Scroll up to the top so user more easily sees the filtered data
ActiveWindow.SmallScroll Down:=-30000
End Sub