I am trying to write a Macro that filters on a criteria on "Sheet1" and apply Vlookup on only the filtered cells on "Sheet1" using a lookup table that i name as "Kc" on "Sheet2".

So far my only work around is to copy the filtered cells into another sheet that i name "Sheet3" and do a vlookup using the [Kc] table on "Sheet2", after that I remove filter on "Sheet1" and do a vlookup between "Sheet1" and "Sheet3".

I am new to VBA and this will be really helpful with my work. Thanks 

 

Sub Filter_Test2()

If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A1").AutoFilter
End If

ActiveSheet.Range("A1:O143").AutoFilter field:=2, Criteria1:=">=95", visibledropdown:=False
Sheets("Sheet1").Activate
Range("B1 : C145").Select
Selection.Name = "kc"
Sheets("Sheet2").Activate

Dim x As Long
For x = 2 To 140
On Error Resume Next

Range("C" & x).Value = Application.WorksheetFunction.VLookup(Range("B" & x), [kc], 2, False)
Next x

End Sub