I filter columns of data, A12:I350, based on two cell values: B8 and L10.
The code works but I need to open the VBA module and run it every time I change the cell value. I would like the columns to auto filter once the cell value is changed.
The current code:
Option Explicit
Sub FilterBasedOnCellValueAnotherSheet()
Dim product1, product2 As Range
With Worksheets("Compensation Eval - Template")
Set product1 = .Range("B8")
Set product2 = .Range("L10")
End With
With Worksheets("Compensation Eval - Template")
With .Range("A12:I350")
.AutoFilter Field:=2, Criteria1:=product1, Operator:=xlOr, Criteria2:=product2
End With
End With
End Sub
Following this question and answer: VBA Filter based on another cell
I tried:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 3 Then
Sub FilterBasedOnCellValueAnotherSheet()
Dim product1, product2 As Range
With Worksheets("Compensation Eval - Template")
Set product1 = .Range("B8")
Set product2 = .Range("L10")
End With
With Worksheets("Compensation Eval - Template")
With .Range("A12:I350")
.AutoFilter Field:=2, Criteria1:=product1, Operator:=xlOr, Criteria2:=product2
End With
End With
End If
End Sub
End Sub
The C2 cell value (Target.Row = 2 And Target.Column = 3) is changed using a formula that fills from another worksheet.
Due to the information from below I chose to make the field that starts the module upon change J4. This way a new value must be manually entered when the user wants to filter the data. I will also filter the data based on the field J4.
The working code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 4 And Target.Column = 10 Then
Dim product1 As Range
With Worksheets("Compensation Eval - Template")
Set product1 = .Range("J4")
With .Range("A12:I350")
.AutoFilter Field:=2, Criteria1:=product1
End With
End With
End If
End Sub
The issue was solved by moving the code from a module to the worksheet.
Try using this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 3 Then
Dim product1 as Range, product2 As Range
With Worksheets("Compensation Eval - Template")
Set product1 = .Range("B8")
Set product2 = .Range("L10")
With .Range("A12:I350")
.AutoFilter Field:=2, Criteria1:=product1, Operator:=xlOr, Criteria2:=product2
End With
End With
End If
End Sub
Note: Your comment "it does not seem to work" makes it a little tricky to diagnose, but in case you're not aware, this code needs to be pasted into the worksheet object in order to fire when a change is made to that worksheet.