More than just an auto filter....

Posted by Robin on January 17, 2001 9:15 AM

Hi,

I have a chunk of data in columns detailing usernames, ID, phone number and which department they belong to.
I need to be able to produce a list of staff in each department. "AUTO FILTER" I hear you cry - but here is the catch - I need this list to appear on a separate worksheet to the data and for the list to be "interactive" ie a user can pick a department from a dropdown list and hey presto, all users for that department are listed underneath!

Any one got any bright ideas?

Thanks,

Robin

Posted by Dave Hawley on January 18, 2001 1:19 AM

Hi Robin

here is one way, using VBA. I will assume:
your table with all info on is called Sheet1.
You have headings in sheet1 row 1.
Your Department Column is A.
You Department list will be in cell F1 of sheet2.

1. Name your list of Departments range e.g "Deps"

2. select sheet2 cell F2 and go to Data>Validation and using the "List" option, place in the range for your departments, e.g $A$2:$A$100

3. In any other cell on sheet2 put: =Deps

4. Right click on the sheet2 name tab and select "View Code". Paste in the code below over the top of what you see.

Private Sub Worksheet_Calculate()
On Error Resume Next
If ActiveCell.Address &LT;&GT; "$F$2" Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False

If Sheets("Sheet1").AutoFilterMode = False Then
Sheets("Sheet1").Rows(1).AutoFilter
End If
Sheets("Sheet1").Cells(1, 1).AutoFilter Field:=1, Criteria1:=ActiveCell
Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeVisible).Copy
Sheets.Add().Name = ActiveCell
ActiveSheet.Paste
Sheets("Sheet1").AutoFilterMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.CutCopyMode = False
End Sub

Change any ranges and sheet names to suit. Push Alt+Q and save.

Now each time you select a different department from your list a new sheet will be created called the department chosen. And all data pertaining to this department will be on the sheet.

Hope this helps
Dave
OzGrid Business Applications