Autofilter Data to separate worksheet

  • Thread starter Thread starter yolanda.silva
  • Start date Start date
Y

yolanda.silva

Hi :) I've been working on a spreadsheet for work and I have somewhat
minimal experience with excel... what I'm trying to do is have one
master worksheets that would house all information and then on
separate worksheets have autofilters in place to gather just some
information... to be more specific - this spreadsheet is being used to
list employees within a department that are on corrective action. The
columns will be:

ID / Name / Hire Date / Level of Corrective Action /
Reason / Date / Supervisor

So, the main page is where all supervisors will have access to enter
their data. I have a macro in place that auto sorts the information
on startup so that it is first by supervisor name and then
alphabetical... BUT... what I'm wanting is...

On separate tabs (by supervisor name) - I would like to have it
autofilter all the representatives (no matter what the level of
corrective action) for that particular supervisor. I realize that it
can be done on the master - but I don't want someone to mistakenly
save that filter and then suddenly I get 30 phone calls trying to
figure out why only "John Smith's" people are showing up :)

So, on worksheet called "John Smith" I would like it to filter out all
the data from the master sheet that have John Smith in the Supervisor
field. I'm using drop down boxes in the Supervisor field so it'll be
uniform - won't have to worry about people entering things in various
formats.

Is this possible? Can someone tell me what the VB code might be for
it? Or get me started on it?

Thanks!!! :)
 
If you have a macro that does automatic sort at the beginning, you could add
one line that removes existing filtering (ActiveSheet.ShowAllData).

If you absolutely want a separate sheet for each Supervisor, then select all
these sheets together (click on the first one, then press Shift key, then
click on the last one while shift key is still pressed), then put in cell A1:
=SheetMaster!A1, then extend the formula to the whole range (add plenty of
blank rows at the bottom, so future additions in the master table will also
appear on individual sheets. Now select the master sheet in order to
deselect all the other sheets, then go to each sheet and use Data > Filter >
Autofilter and pre-select the Supervisor name.

Stephane.
 
What you are asking for requires the use of a macro. If this is what you are
looking for, then supply more details of your requirement and answers should
be forthcoming.

Vaya con Dios,
Chuck, CABGx3
 
I tried both of these options... the first one... I guess I'm doing
something wrong? Do I put that command for "thisworkbook" or that
particular page? Also - should it actually say
"ActiveSheet.ShowAllData" or am I calling the ActiveSheet whatever the
page is? (I'm sorry - I really know almost nothing about VB or macros
- someone else helped me make the macro to have it sort the data
alphabetically). It seemed that in order to get it to show all the
rows again I had to go and UNSELECT the "autofilter" button again...

With the second option - I managed to get it to copy all the data...
the only problem is - it won't let me autofilter from these other
pages. Because it's a formula, I presume? It has no option to filter
anything... so that's not a viable solution...

Thanks! :)
 
Back
Top