How do I filter data from a master worksheet to other worksheets

[Guest]

I am pretty new to this so excuse my ignorance.

I finally got Auofilter to work so i am pretty happy.

We have a master sheet which has all the tasks all employees are working on.
We use an x to dictate a column where someone has to do it.

The master work sheet may look like this

Date Task EmpName1 EmpName2 EmpName3
1-nov Call client x
1-nov Attend meeting x x
2-nov Photocopy Doc x


I created a workheet for each employee by referencing everything in the
master sheet (i.e for a1 in EmpName1 i just put =master!a1) - and then
applying an autofilter with "x" as the criteria for each employee worksheet.

so the EmpName1 worksheet would look like this

Date Task EmpName1 EmpName2 EmpName3
1-nov Call client x
1-nov Attend meeting x x


This all works really well except when new tasks are added to the Master
worksheet - for the task to show up on each employees worksheet they need to
go and "re-filter" using the x selection to bring up the new tasks. Ideally I
would like this to be automated. is this possible?

Thanks in advance

 

[Roger Govier]

Hi Dave

One way would be to use Advanced Filter rather than Autofilter.
For more help on Advanced Filter, take a look at Debra Dalgleish's site
http://www.contextures.com/xladvfilter01.html
and for Dynamic Ranges
http://www.contextures.com/xlNames01.html#Dynamic


Define a Dynamic range to reference your data on Master sheet
Insert>Name>Define>Name Mydata Refers to
=OFFSET($A$1,0,0,COUNT($A:$A),COUNTA($1:$1))
This assumes that your header row on Master starts in A1

On each employee sheet, in cell A1 enter Employee1 (the name as it
exists on your master sheet)
In cell A2 enter ="=x"

Delete everything else on the sheet. Repeat the procedure for all other
employee sheets, changing just the name in A1, but use the same formula
in A2.

Copy the following short piece of code into a module in your workbook
(instructions on how to do this at end)

Sub UpdateEmployee()
Dim colno As Long, dest As Range
If ActiveSheet.Name = "Master" Then
MsgBox "Please go to Employee Sheet before running this macro"
Exit Sub
End If
colno = Workbooks.Application.CountA(Sheets("master").Range("1:1"))
Set dest = Range(Cells(4, 1), Cells(4, colno))
Sheets("Master").Range("Mydata").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:A2"), CopyToRange:=dest, Unique:=False
End Sub

Then, on each employees sheet, press Alt+F8 which will bring up the
Macro dialogue box and choose Run with Update Employee highlighted.
This will update the employee sheet for any changes that have taken
place in the Master sheet

You can copy the code and paste it into your Visual Basic Editor
(VBE) in a Standard Module located in your file.
To do this,
Alt + F11 (open VBE)
Ctrl + R (open Project Explorer)
Select the file name on the left
Insert > Module
Paste code in Module

David McRitchie has lots of useful help about installing macros on his
site at
http://www.mvps.org/dmcritchie/excel/install.htm
http://www.mvps.org/dmcritchie/excel/getstarted.htm