VBA for Autofilter if column ontains value on another sheet

[Godders199]

Hello, I am trying to work out the vba, to replace "pass" and "investment" with the cell location Sheet Instructions cell C26 and Sheet instructions C35 as these are drop down lists which can contain various options. I can find numerous examples where everything is contain on a single sheet, but not different sheets, if anyone has an example i would appreciate it.


Sheets("cases available ").Select
Cells.Select
ActiveSheet.Range("$A$:$AA$").AutoFilter Field:=11, Criteria1:= _
"=*Pass*", Operator:=xlAnd

ActiveSheet.Range("$A$:$AA$").AutoFilter Field:=27, Criteria1:= _
"=*investment*", Operator:=xlAnd

I also need to know the code, for if c26 is blank , then look at cell c32 on that sheet.

Thanks

 

[Fluff]

Try
Criteria1:=Sheets("Instructions").range("C26").value

 

[Godders199]

THanks, I have tried this and am nearly there on the first part., however field 27 can have various word, i only want it to filter if it contains the word in cell c35, I have tried below and this does not work

ActiveSheet.Range("a:ab").AutoFilter Field:=8, Criteria1:= _
Sheets("instructions ").Range("c23").Value
ActiveSheet.Range("a:ab").AutoFilter Field:=11, Criteria1:= _
Sheets("instructions ").Range("c26").Value
ActiveSheet.Range("a:ab").AutoFilter Field:=27, Criteria1:="=*" & Sheets("instructions ").Range("c35") & "*"

How to i make the last line into contains "xyz"

 

[Fluff]

A simplified version of your code is

   With Sheets("cases available ").Range("a:ab")
      .AutoFilter Field:=8, Criteria1:=Sheets("instructions ").Range("c23").value
      .AutoFilter Field:=11, Criteria1:=Sheets("instructions ").Range("c26").value
      .AutoFilter Field:=27, Criteria1:="=*" & Sheets("instructions ").Range("c35") & "*"
   End With

and it works for me. What are the values you have in col AA & what are your filter values?

 

[Godders199]

my filter values are

[TABLE="width: 134"]
<tbody>[TR]
[TD]my filter values are
C23 is a list of names
c26 is a list of status values
c35 are a list of products[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Example of cell in column aa

[TABLE="width: 98"]
<tbody>[TR]
[TD]estate investment protection [/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

or
[TABLE="width: 98"]
<tbody>[TR]
[TD] protection Mortgage[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
So there are gaps between each word and sometimes before the first word.
column AA, just combines the values in columns V-Z as i thought it would be the easiest way to filter to find the rows i want .

SO effectlvely i am looking for a filter for , name , status and any row in column AA that contains the selected product.

 

[Fluff]

How are you concatenating columns V-Z?

 

[Godders199]

HI this is the formula i am using.

=CONCATENATE(V7," ",W7," ",X7," ",Y7," ",Z7)

 

[Fluff]

In that case if you have estate investment protection in col AA and estate in C35 it should work. Try just running the autofilter on col AA & see what happens

With Sheets("cases available ").Range("a:ab")
      .AutoFilter Field:=27, Criteria1:="=*" & Sheets("instructions ").Range("c35") & "*"
End With

 

[Godders199]

Thanks got it all working now

 

[Fluff]

Glad it's sorted & thanks for the feedback