Table Criteria based on cell value?

[gino59]

Hi all,

I'm trying to figure out how to specify an Excel 2010 table filter by reading the value in a cell located on a different worksheet.

I have the table on sheet "mData1". I'd like to filter that table based on the cell value on sheet "MBR" in cell R4.

Here's a snippet of the code...

    Sheets("mdata1").Activate
    ActiveSheet.ListObjects("mData").Range.AutoFilter Field:=3, Criteria1:= _
        Sheets("MBR") & Range("R4").Text

How can I specify the criteria to read the value in cell R4 on the MBR worksheet and then filter the table on the mData sheet?

Many many thanks in advance!!

Cheers,
Gino

 

[Joe4]

Untested:

What if you use

, Criteria1:= Sheets("MBR").Range("R4").Text

for the criteria?
Does that work?

 

[gino59]

Thanks for the reply, Joe4 - yes, that worked!!

Someday, maybe I might just figure out syntax! I actually tried to get creative and decided I need more than just the month filter and added a year filter. Concatenated both fields into R5 and defined a name for that cell (MOsel). I changed the code to:

     Sheets("mData1").Activate
     ActiveSheet.ListObjects("mData").Range.AutoFilter Field:=1, Operator:= _
          xlFilterValues, Criteria2:=Array(1, MOsel)

and now I can filter via two criteria (month & year).






Many thanks again!!

Cheers,
Gino