Individual Contributor Tabs from a Master Spreadsheet

Hi all -
I know this contains similar components to some other requests, but also I think a twist.

From a master (data entry) spreadsheet of work projects, I'd like to automatically create a tab for each individual contributor that lists all projects they are involved with. The thing is that being a contributor can mean being the lead, assisting with, or being the technician on the project (all columns in the master). To make the individual tabs easier to read, it'd be great to have that tab's contributor name in red, wherever it happens to fall in the various columns.

I've created an example that's a little simpler than reality - in real life there would be 20 tabs for the individual contributors and instead of 3 "Assist" columns there would be 20 columns.

Thanks much.

Hello,

I have done some coding to your workbook, Press Alt F8 and run Tabs_From_Rows.

The Focus was put into creating the tabs and sorting the data from the Master sheet. This part of the code cannot be taken any further as i dont have the required information (number of columns used, Column Heading) Code will work ONLY up to column H.

Take a look at the progress and post back with any information you think i need to be able to finish the codeing.

cheers.

This should do it. I tried to make it flexible so it will work on any size sheet, when it checks for "matches" it skips columns A:C, it will use all the others. It moves data by matching cell values to the sheet names...so the sheet named "Washington" will get all the rows with the exact match "Washington" in any row.

Please Login or Register  to view this content.

==========
How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. Press Alt-F8 and select ParsePeople from the macro list.

==========
Since this version of the macro works off of EXISTING sheets, if you add a new name/asset anywhere you would need to add a blank sheet of the same name to receive the data. We could make the macro create sheets as needed, too, if that seems necessary.

reneeh63. JB's solution seams to have competed your needs. if so please read the forum rules and mark the thread as solved.

JB you solution with the use of the formular to cheek the sheet name agenst the row was a spark of XL genius.
I was going to use it in the tab creation code untill i found that the speed of the .find or formular recalculate was supprizling slow. i put this down to the fact that the .find must be allong the lines of for each cell in range loop. ill have to test on xl2010 to see if microsoft has inproved the .find code. im not trying to take away from the code at all though just supprized at the speed of the native function. i put in some test if you are intrested.

cheers

Wonderful - I just added columns and it works like a charm! Thanks again!

I did try to make it robust enough to handle anything beyond the first 3 columns. Glad it worked!

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

==========
D_Rennie:

Interesting on the time issue. How much faster would your sub be if you didn't start out by removing the existing sheets?

If I was to restructure the doc to create sheets, I wouldn't evaluate every cell over and over, I would:

1) insert the key formula in an empty row as you've already seen demonstrated
2) use the Adv. AutoFilter to create a quick unique list of values from all cells, slip them into an array in memory
3) apply the regular AutoFilter to each of those unique values one-time each in the key column to get all the matching rows at once
4) add a one-time check to create blank missing sheets properly named

This would probably still be pretty fast in comparison since each row will only be "touched" a few times total each...
1) add the formula
2) unique values copied out one time
3) the row is copied once for each unique value in the row
4) remove the key formula

Even if the data were 50 columns wide, it would still be pretty robust this way, I think.

I reason i ran the test is becouse i though that using the inbuilt function would be lightning fast. and when i tested it last night i got variable times though it mostly leveled out at .25 -.3 and 6.? using the .find ect. though im now starting to think that my computer is just doing what it wants to, as the same test done now provided what i 1st supected.
Tabs_From_Rows took 0.25 seconds. Without sheet deletion
Tabs_From_Rows took 0.28125 seconds.

ParsePeople took 0.078125 seconds.
showing that every thing i read about the fact of inbuilt function being lightning fast.
last night I though there must have been somthink wrong with the timer as i couldnt see how my code would run faster with the amount of loops in it. ill test it again in the morning just to prove my computer is worth the paper its written on.
i suppect you got times showing like above?
like i said i wasnot trying to take anythink away from you, I still think that your idear shows a better approach. i just couldnt belive the results i was getting.
I guess the point is when trying to test somthink as fast running as this you are kinda wasting your time as the cpu doesnot even warm up before its finished.
edit tested again after writing this near constants of ParsePeople took 6.298828E-02 seconds.
Tabs_From_Rows took 0.2807617 seconds. like what i was seeing last night, so i go back to the underline point