Dynamic filter

[2022]

I've got a sub where I want to copy a cell which has been selected by a user, then go to a column in another workbook then filter that column by the cell I've just copied in the first workbook.

Is this possible?

I've done the steps using the recorder, but it didn't give me what I expected.

In the example below, I'm copying cell A2 in Sheet1 of a file called "File1" then going to another file called "FilterFile" then applying the filter then pasting it.

In this case, it just pasted the word "Cake" (which was in cell A2 in the first sheet). But is there a way to make it paste whatever word was in cell A2 in the first sheet? Eg if it was "Crisps"?

Sub filter_2()
    Windows("File1"). _
        Activate
    Sheets("Sheet1").Select
    Range("A2").Select
    Selection.Copy
    Windows("FilterFile.xlsx").Activate
    ActiveSheet.Range("$A$1:$BG$498941").AutoFilter Field:=4, Criteria1:= _
        "Cake"
End Sub

 

[kgkev]

you can store the value of the selected cell into a variable, so it can be used later.

Sub filter_2()
Dim Filter_String as String                                             'this defines what type of variable you are creating.
    Filter_String = ActiveCell.Value                                  ' this takes the contents of the active cell and stores into the variable
    Windows("FilterFile.xlsx").Activate                              'Actives your other workbook
    ActiveSheet.Range("$A$1:$BG$498941").AutoFilter Field:=4, Criteria1:=Filter_String    'creates the filter (untested)
End Sub

 

[2022]

Hmmm….that didn’t work, so I’ll try to just copy the cell into the Filter file, then reference that value in the filter.

Can I just change the criteria in the filter to the cell value?

ie if I pasted the cell I’d just copied into cell Z1 of the filter file, could I put “Z1” in the part where it says “criteria” in the filter?

 

[kgkev]

What was the error you received?
does it give you a debug box, if you click debug which line is the error on?
Have you manually cleared any previous filter applied?

Can you properly define the range you wish to filter?