MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to insert row, sort list and insert sheet with corresponding name, help?


Posted by Brad on June 28, 2001 5:48 AM

Hello-
I have a workbook that has a sheet for each of 10 employees. There is a tally sheet at the end that sums all the employees hours by job in a matrix. The employees are listed alphabetically down column B (First name in A and Last name in B). I want to make a macro to insert new employees when needed. It needs to make a sheet with the employees last name that is the same as a template sheet "Template". It also needs to add the employee into the matrix and then sort the matrix. I do have an average of the hours at the bottom so I can't just use a whole column sort, it has to be able to tell how many employees I have (column A is blank under the last employee and column B contains the text "Average"). I would also like the sheet with the Last name to be place alphabetically between the other sheets. Any help is greatly appreciated.

Brad


Posted by Brian P on June 28, 2001 11:27 AM

Pivot?

Brad,

Have you tried pivot tables? Have you considered having all your data on one tab(sheet) and using a pivot table to summarize and create a separate tab for each employee? On the pivot table you would have the employee as the page field and then do show pages to create all of the tabs.
Just curious.

Brian

Posted by Brad on June 29, 2001 1:25 PM

Re: Pivot?

Brian-
I haven't thought of that. But I still need to know how to do the macro with inserting the sheets...unless the pivot table would eliminate that????

Brad

Posted by Brian P on July 02, 2001 10:21 AM

Re: Pivot?

Brad,

I think the pivot table would work best.
If you're the one updating the worksheet - pivot tables should be fine. If you're having multiple users update - then maybe another approach would be better. If you'd like I can step you through it - email me at
infinityfaro@hotmail.com

Brian P -