Create new Excel sheets from a "Master" sheet

[WaltW]

I have an Excel "Master" employee list, which includes the supervisor for each employee. I would like to automatically create a new, separate sheet for each supervisor, with only the employees they supervise listed on their sheet. Is there an easy way to do this in Excel besides cut and paste? This is a huge list and will take hours to create the supervisor sheets manually.

Thanks for any help offered!

WaltW

 

[billswitzer]

If each employee has their supervisor listed can you do a range sort by supervisors with the secondary sort in ascending order of the employees. That should give you a range to print out for each supervisor with their supervised employees listed alphabetically.

 

[DaleWatson123321]

Hi Walt,

Excel has a powerful (automatic via VBA) "extraction" capability that will fit your needs VERY well. It permits one to extract data selectively, based on "criteria" you specify. In your case, the criteria would be the supervisor's names.

On Excel's menu, this option is: Data - Filter - Advanced Filter. However, if you've already explored this option, you possibly encounter ERROR messages. There are two (FALSE) error messages that say one can NOT extract data to a SEPARATE sheet, and can NOT place the criteria on a SEPARATE sheet.

I suppose one could say these ERROR messages are "TRUE" IF you attempt to use this option "manually" - because it does NOT work - because the ERROR message causes a sudden STOPPAGE in your attempt.

I am of course being facetious in saying the messages are "TRUE". More exactly, these messages are really "BUGS".

Using VBA, the extraction process works without error.

With the extraction process working so quickly and easily (via VBA, just the click of a button), you might decide you only need to have ONE (common) extraction sheet - that you would use to extract and print reports for each supervisor. You could have the option to print any ONE supervisor's report, or you could choose to print reports for ALL the supervisors - all at once with just a click of a button.

If your Master sheet has other relevant human resource data, you might also want to use this same extraction process for generating other types of reports.

Another possibility, is to create "Summary" reports - that have a matrix of totals - for example: by supervisor by month, or supervisor by year, or whatever is appropriate for your actual data. These "totals" are generated by using Excel's "database formulas" = e.g. =DSUM, =DCOUNTA, etc.

Because this part of Excel is NOT well documented, you probably could use some help in getting started. If you would like help, I could email you a variety of example files, or if you like, you could email me your file and I would set it up for you.

If you happen to have sensitive data, replace it with fictitious data that still reflects the nature of your type of data. And I don't require ALL the data, only enough records to provide a reasonable example.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca