Custome Filter Macro?

[doug.washburn]

Hi there,

I'm using Excel 2007 and I have 2 tables (each on seperate tabs). The first table has a list of user names and a list of numbers beside their names that correspond to numbered tasks in the second table.

What I would like to do is when I select a users name (or enter it into a certain field), the macro (or formula) will look at the user name, go to the second table and filter that table to only show the tasks they are assigned.

the first table looks like this:

Employee Objectives
Doug Washburn 1 2 3 4
Brian Duggan 1 3 4 8
Melanie Labaj 1 3 4 5
Scott Arbuthnot 1 2 3 4 5
Bernie Cyr 1 2 3 4 7 14
Nino Infante 1 2 3 4 6

The numbers beside each name correspond to a numbered list on the second tab, so I need to find a way to do this custome filter. Any suggestions?

Thanks

 

[Smitty]

If you use a Data Validation list for the names you could then use a change event to drive the filtering criteria.

If you record a macro applying the filter to one user, you can then add that to the code and replace the recorded criteria with the list cell/range.

Here's some boilerplate change event code:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("xxx")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[doug.washburn]

Here is the macro as it recorded:

' Filter Macro
'

'
Range("A14").Select
Sheets("Objectives").Select
ActiveSheet.Range("$A$3:$F$40").AutoFilter Field:=1, Criteria1:=Array("1", _
"2", "3", "4"), Operator:=xlFilterValues
ActiveWindow.SmallScroll Down:=-12
Sheets("Objectives").Select
Range("A1").Select
End Sub

I'm not too sure how to replace the array of 1, 2, 3, 4 with the range of numbers beside each user's name.

Sorry, I'm a little thick on this one.