+ Reply to Thread
Page 1 of 7 1 2 3 ... LastLast
Results 1 to 15 of 99

Consolidate workbooks

  1. #1
    Forum Contributor
    Join Date
    07-31-2008
    Location
    UK
    Posts
    104

    Consolidate workbooks

    Hi all

    Need some help. There is now a requirement for my team to complete timesheets. Something like the attached. I have 2 sheets on there but that will actually be 2 workbooks, as an example

    The requirement is for each individual to complete their timesheet and drop in a folder. Our manager will then see 15 timesheets in this folder. He would then need to consolidate the timesheets into ONE workbook and ONE worksheet.

    They want to do some analysis etc so would need a total of all timesheets.

    However if you look at the timesheet - each could be different depending on how many clients that person has worked for that week. Person 1 may have 5 clients that period whilst Person 2 may have just the one.

    Any idea's how this can be achieved please?

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,748

    Re: Help - need to consolidate workbooks

    Hi yus786
    What do you wish the results to look like?
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: Help - need to consolidate workbooks

    Hi... try this.
    This will allow the user to open any number of files, then copy the contents into a single sheet in the workbook. Note that it locates the paste position by finding the last used row in column A, then moves down 5 more rows (to accommodate the additional rows. That can be modified pretty easily. Let me know if this is what you're after!

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    07-31-2008
    Location
    UK
    Posts
    104

    Re: Help - need to consolidate workbooks

    Quote Originally Posted by jaslake View Post
    Hi yus786
    What do you wish the results to look like?
    Hi, i'm up for suggestions. We want to be able to interogate the data say, EE1 worked for 14 hours on Client 1 and EE2 did 2 hours etc.

    Maybe keep the results like one of the timesheets?

  5. #5
    Forum Contributor
    Join Date
    07-31-2008
    Location
    UK
    Posts
    104

    Re: Help - need to consolidate workbooks

    Quote Originally Posted by JP Romano View Post
    Hi... try this.
    This will allow the user to open any number of files, then copy the contents into a single sheet in the workbook. Note that it locates the paste position by finding the last used row in column A, then moves down 5 more rows (to accommodate the additional rows. That can be modified pretty easily. Let me know if this is what you're after!

    Please Login or Register  to view this content.
    Thanks, i'll have a play now and report back

  6. #6
    Forum Contributor
    Join Date
    07-31-2008
    Location
    UK
    Posts
    104

    Re: Help - need to consolidate workbooks

    Quote Originally Posted by JP Romano View Post
    Hi... try this.
    This will allow the user to open any number of files, then copy the contents into a single sheet in the workbook. Note that it locates the paste position by finding the last used row in column A, then moves down 5 more rows (to accommodate the additional rows. That can be modified pretty easily. Let me know if this is what you're after!

    Please Login or Register  to view this content.
    Hi, i get the following error

    error.jpg

  7. #7
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,748

    Re: Help - need to consolidate workbooks

    Hi yus786
    I can't answer this for you
    What do you wish the results to look like?
    You need to define it.

    And, please don't quote entire posts...quote only relevant parts.

  8. #8
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Re: Help - need to consolidate workbooks

    That error is a new one for me - I'll check into it, but please proceed with John... he's got WAAAAAAAAAAAAAY more experience than I do!

  9. #9
    Forum Contributor
    Join Date
    07-31-2008
    Location
    UK
    Posts
    104

    Re: Help - need to consolidate workbooks

    Sorry John. Lets go with keeping the results like one of the timesheets? I'll then be able to use pivot to interrogate the table.

    So 15+ timesheets consolidated into one timesheet

  10. #10
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,748

    Re: Help - need to consolidate workbooks

    @yus786
    Invalid Outside Parameter would indicate you have a line of code outside the Procedure

    Please Login or Register  to view this content.
    @JP
    Nah, not really...I admire your work...but thanks for the compliment.

  11. #11
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,748

    Re: Help - need to consolidate workbooks

    Hi yus786
    We're not communicating well. What I'd like you to do is take the two time sheets in your sample file and put them together in a results worksheet so we have a picture of what you expect the results to look like.

  12. #12
    Forum Contributor
    Join Date
    07-31-2008
    Location
    UK
    Posts
    104

    Re: Help - need to consolidate workbooks

    Thanks, added the proc but niw getting error here -
    Sheets("<i>sheetname</i>").Select
    Runtime error 9 - subscript out of range

  13. #13
    Forum Contributor
    Join Date
    07-31-2008
    Location
    UK
    Posts
    104

    Re: Help - need to consolidate workbooks

    Quote Originally Posted by jaslake View Post
    What I'd like you to do is take the two time sheets in your sample file and put them together in a results worksheet so we have a picture of what you expect the results to look like.
    Will do John

  14. #14
    Forum Contributor
    Join Date
    07-31-2008
    Location
    UK
    Posts
    104

    Re: Help - need to consolidate workbooks

    Hi John. I have attached the total timesheet. This is the total of all timesheets. As you can see, one of the previous timesheets had one one client short, but has been manually added by me.

    This would be the real life scenario. Some employee would be working on say 2 clients per week whilst others would be working on 8 as an example

    Thanks for all your help
    Attached Files Attached Files

  15. #15
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,748

    Re: Help - need to consolidate workbooks

    Hi yus786
    Got the Total file...need to study it...initial impression...isn't gonna be easy.

+ Reply to Thread
Page 1 of 7 1 2 3 ... LastLast

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