Assign active cell value to a filter as criteria in another sheet

Question

I'm trying to use an active cell value as the criteria for a filter on another work sheet.

My Active cell value (in the first sheet ("WIthout Dupes") should be the value of filter criteria in the second sheet ("Master"). So ultimately my code should not have Range("B$142").Select but the Active cell value and the criteria1 should be referring to the Active cell value of the first sheet.

I tried to use

 Sub test1()
'
' test1 Macro
'

'
    Range("B$142").Select
    Selection.Copy
    Sheets("Master").Select
    ActiveSheet.Range("$A$1:$AR$1787").AutoFilter Field:=1, Criteria1:= _
        "00065062"
    Columns("X:X").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("WIthout Dupes").Select
    Range("B145").Select
    ActiveSheet.Paste
    Sheets("Master").Select
    Application.CutCopyMode = False
    ActiveSheet.ShowAllData
    Range("A1").Select
    Sheets("WIthout Dupes").Select
    Range("B143").Select
    ActiveCell.FormulaR1C1 = "done"

End Sub

score 0 · Answer 1 · answered Jun 21 '18 at 19:17

Try the following code:

Sub Testing ()

Dim k As Long
k = Worksheets("WIthout Dupes").Cells(142, "B").Value

Sheets("Master").Select
ActiveSheet.Range("$A$1:$AR$1787").AutoFilter Field:=1, Criteria1:= k
Columns("X:X").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("WIthout Dupes").Select
Range("B145").Select
ActiveSheet.Paste
Sheets("Master").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
Range("A1").Select
Sheets("WIthout Dupes").Select
Range("B143").Select
ActiveCell.FormulaR1C1 = "done"

End Sub

This should declare the criteria as whatever that cell's value is and filter based on that value.

score 0 · Answer 2 · edited Jun 22 '18 at 06:02

Thanks @Analyst123456789 . I myself wrote the following and that worked too. If you can let me know which one is better with respect to time taken etc. i will be grateful.

Sub test1()
'
' test1 Macro
'

'
Do Until ActiveCell.Value = ""

    PID = ActiveCell.Value
    Selection.Copy
    Sheets("Master").Select
    ActiveSheet.Range("$A$1:$AR$1787").AutoFilter Field:=1, Criteria1:=PID
    Columns("X:X").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("WIthout Dupes").Select
    ActiveCell.Offset(3, 0).Select
    ActiveSheet.Paste
    Sheets("Master").Select
    Application.CutCopyMode = False
    ActiveSheet.ShowAllData
    Range("A1").Select
    Sheets("WIthout Dupes").Select
    ActiveCell.Offset(-2, 0).Select
    ActiveCell.FormulaR1C1 = "done"
    ActiveCell.Offset(-1, 1).Select
Loop

End Sub

If time/speed is a relevant factor I recommend not to use `.Select` at all. This slows down your code **a lot** and is not necessary: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Jun 22 '18 at 06:01

Assign active cell value to a filter as criteria in another sheet

2 Answers2