+ Reply to Thread
Results 1 to 8 of 8

Individual Contributor Tabs from a Master Spreadsheet

  1. #1
    Registered User
    Join Date
    01-05-2010
    Location
    Illinois, US
    MS-Off Ver
    Excel 2007
    Posts
    5

    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.
    Attached Files Attached Files
    Last edited by reneeh63; 01-16-2010 at 10:26 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Individual Contributor Tabs from a Master Spreadsheet

    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.
    Attached Files Attached Files


  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Individual Contributor Tabs from a Master Spreadsheet

    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.
    Attached Files Attached Files
    Last edited by JBeaucaire; 01-09-2010 at 06:23 AM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    01-05-2010
    Location
    Illinois, US
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Individual Contributor Tabs from a Master Spreadsheet

    Thanks! This works great - I tried it out just adding projects with the existing contributors and then added a tab for a new contributor and all looks good. I'm kinda afraid to see what happens if I add columns for those cases when a project has more than 3 assisting people on a project...ha!

  5. #5
    Registered User
    Join Date
    01-05-2010
    Location
    Illinois, US
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Individual Contributor Tabs from a Master Spreadsheet

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

  6. #6
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Individual Contributor Tabs from a Master Spreadsheet

    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
    Attached Files Attached Files

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Individual Contributor Tabs from a Master Spreadsheet

    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.
    Last edited by JBeaucaire; 01-10-2010 at 03:35 AM.

  8. #8
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Individual Contributor Tabs from a Master Spreadsheet

    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
    Last edited by D_Rennie; 01-10-2010 at 11:47 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1