Excel VBA Auto-filter Based on Values of Another Cells

Posted on by Summa Lai

I and was trying to filter the data based a couple of cell values. Finally, I figured it out.

Below, please see my sample. Filler Sheet “Trans” based on the values of C3, C5 and C6 from Sheet ” Reports”.

Sheets("Report").Select
Range("C4").Select
store = ActiveCell.Value
Range("C5").Select
sdate = ActiveCell.Value
Range("C6").Select
endate = ActiveCell.Value

Sheets("Trans").Select
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If

Range("A1:L" & LastRow).Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:= _
    store
Selection.AutoFilter Field:=2, Criteria1:= _
">=" & sdate, Operator:=xlAnd, Criteria2:="<=" & endate

Range("A1:L" & LastRow).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheet “Reports”
Sheet “Trans”