Pasting a value into Filter search field using VBA Macro

[matthewfreestone]

Hi All,

Is there a way to use VBA to paste a value which has been copied (through VBA) into the Filter field. When recording my macro I start by using -

Range("E19").Select
Selection.Copy

However when I paste in VBA it pastes the value that is there currently. For example ...

ActiveSheet.Range("$A$1:$T$11596").AutoFilter Field:=5, Criteria1:= _
"Apples"

The problem that I have is that E19 is a field that will change based on information that is added to this sheet every day. So today it may show Apples, but tomorrow it may show Oranges, though the Macro has recorded to set the filter to Apples again.

Any help would be greatly appriciated.

Thanks,
Matt

 

[BarryL]

ActiveSheet.Range("$A$1:$T$11596").AutoFilter Field:=5, Criteria1:= _
 Range("E19").Value

 

[matthewfreestone]

Hi BarryL

Thanks for the response, however I missed one piece of quite important information... the data is Field E19, is in Workbook 1, and the data that needs filtering is in the range ("$A$1:$T$11596") on a seperate Workbook. Hence why I thought we needed a copy/paste solution.

Thanks,
Matt

 

[Fishboy]

Hi BarryL

Thanks for the response, however I missed one piece of quite important information... the data is Field E19, is in Workbook 1, and the data that needs filtering is in the range ("$A$1:$T$11596") on a seperate Workbook. Hence why I thought we needed a copy/paste solution.

Thanks,
Matt

Hi Matt,

Does this get you moving in the right direction?

Sub YourMacroName()
Dim FilterString As String
FilterString = Workbooks("[COLOR=#ff0000][B]Workbook1.xlsx[/B][/COLOR]").Sheets("[COLOR=#ff0000][B]SheetName[/B][/COLOR]").Range("E19").Value
ActiveSheet.Range("$A$1:$T$11596").AutoFilter Field:=5, Criteria1:= FilterString
End Sub

[EDIT] - Obviously amend the bold red parts to suit the correct workbook name and sheet name

 

[matthewfreestone]

Hi Fishboy,

That does indeed look like that will do the trick, will give it a try when back at the office tomorrow. Thanks alot!

 

[avc4567]

Hi Fishboy,

That does indeed look like that will do the trick, will give it a try when back at the office tomorrow. Thanks alot!

Hey fishboy and mathew,

i am able to make macro from above but want to search like below
The current search needs and exact match
%Appl%
so even if apple is not perfect and i have word related to appla or appli then also the output will be shown
any suggestions on the same