+ Reply to Thread
Results 1 to 99 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 Expert 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,749

    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 Expert 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,749

    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 Expert 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,749

    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 Expert 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,749

    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 Expert 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,749

    Re: Help - need to consolidate workbooks

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

  16. #16
    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
    Got the Total file...need to study it...initial impression...isn't gonna be easy.
    OK thanks. Another option would be to change the days and clients over, so the days would be at the top (always 7 days) and the clients down the side.

  17. #17
    Forum Expert 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,749

    Re: Help - need to consolidate workbooks

    Hi yus786
    Seems to me, if I can do this pic1.jpg
    I'll be able to figure out how to do this pic2.jpg

    Gotta figure out how to do the first scenario. I'll work on it...it'll be a while.

  18. #18
    Forum Expert 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,749

    Re: Help - need to consolidate workbooks

    Hi yus786

    I'm attaching 4 files. The attached file "Total" has code that results in a Total file with this format
    change the days and clients over, so the days would be at the top (always 7 days) and the clients down the side.
    To run the macro, click the button. Please note:
    • This has been developed in Excel 2007 and as such will ONLY run in Excel 2007 and later. It CAN be modified to run on systems with pre Excel 2007 installed.
    • Your Time sheet files can be EITHER .xlsx files OR .xls files.
    • All 4 of these files should be in the same folder (with NO OTHER .xlsm, .xlsx or .xls files)
    • Open "Total" file...leave Time sheet files closed.
    • Click the Button on "Total" file...the procedure opens and extracts data it needs from each Time sheet file then closes them.
    • A bit of magic is done on the extracted data and, voilà, hopefully you're looking at what you want to see.
    Let me know of issues.
    Attached Files Attached Files

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

    Re: Help - need to consolidate workbooks

    Wow John, what can i say... thanks for taking the time to help me on this. Really appreciated.

    I'll have a proper play with it tomorrow and report back. One thing jumped out - when you have consolidated the sheets - is there anyway to keep the file name (example - Emp1, Emp2) somewhere too on the table?

    Or maybe have them copied into the worksheets? Just a thought.

    Thanks again and i'll test this properly tomorrow

  20. #20
    Forum Expert 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,749

    Re: Help - need to consolidate workbooks

    Hi yus786

    Probably...but I've not a clue what you're looking for...SHOW ME
    when you have consolidated the sheets - is there anyway to keep the file name (example - Emp1, Emp2) somewhere too on the table
    Show me what you want the report to look like...perhaps we can make it happen...perhaps not. I can't hit moving targets.
    Last edited by jaslake; 02-11-2012 at 10:50 PM.

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

    Re: Help - need to consolidate workbooks

    Hi John, sorry for the delay.

    I have attached a copy of what i would like to see if possible. If possible, would like to see the employee timesheets copied over to the main book as sheets.

    Also, if you check the pivot example i have created - what i would like to see on your MAIN sheet is apart from the totals, i would like to see who has done what hours by means of filter or pivot.

    Thanks for your help, you're a gem
    Attached Files Attached Files

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

    Re: Consolidate workbooks

    I can see that you guys have made a lot of progress, and certainly don't need me here - but wanted to let you know that the reason you got the runtime error on my code is because you didn't change the sheetname. You have to change
    Please Login or Register  to view this content.
    to something like
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    or whatever the name of the sheet where you want all the results is.

  23. #23
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    The attached has this
    would like to see the employee timesheets copied over to the main book as sheets
    I've not yet figured out how to create the Pivot Table (with code). Can do it manually but that doesn't help...the number of sheets will vary and the structure of the sheets will vary.
    Still looking at the Pivot...can't promise anything...not having much luck.
    Attached Files Attached Files

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

    Re: Consolidate workbooks

    Really appreciate your help on this. If you can do the pivot - that would be absolutely brilliant. The purpose of consolidating these timesheets is to do analysis and your help on this will make it happen.

    Thanks again

  25. #25
    Forum Expert 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,749

    Re: Help - need to consolidate workbooks

    Hi yus786
    The Pivot Table doesn't look promissing...been wrestling with it for two days with no breakthrough. If the file structures were the same it'd be a piece of cake. Just had a thought as I was typing this...get back to you.

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

    Re: Consolidate workbooks

    Hi John, something else on the 'total' file. It seems to be totaling the date too??

    I have attached what your sheet does and what it should do (just total the hours). Hope it's not a difficult fix

    Also, rather than keying in the client names or hours - i was thinking of having a pick list. I did create one but open running the macro, it's also bringing over the total rows too. I have attached this also

    Thanks again
    Attached Files Attached Files
    Last edited by yus786; 02-14-2012 at 10:13 AM.

  27. #27
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786

    There's no code in any of these workbooks for me to test.

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

    Re: Consolidate workbooks

    Hi John

    The codes are YOUR OWN ones, i haven't touch them. I just wanted you to see what they do, hence saving as xls. So please run your original sheet Total_new v3.1.xlsm

    So basically running your sheet, it's totaling the dates too i think. And regarding the pickup list - i can fix this by having the list in another sheet

    Thanks

  29. #29
    Forum Expert 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,749

    Re: Help - need to consolidate workbooks

    Hi yus786
    I downloaded the file from my Post #23...this is what I get
    screen shot.jpg
    So, I need you to attach what you're working with that exhibits this
    the 'total' file. It seems to be totaling the date too
    Include in the attachment the code.

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

    Re: Help - need to consolidate workbooks

    John, i just downloaded the SAME file from post 23 and i get this.

    dates.jpg

    I am in the UK and our date format is dd/mm/yyyy - will that be the issue?

  31. #31
    Forum Expert 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,749

    Re: Help - need to consolidate workbooks

    Hi yus786
    Interesting...I'll try to duplicate the issue...get back to you.

  32. #32
    Forum Expert 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,749

    Re: Help - need to consolidate workbooks

    Hi yus786

    I haven't been able to duplicate this
    the 'total' file. It seems to be totaling the date too
    I changed my System Date to date format of dd/mm/yyyy and it had no impact on the results.

    You mentioned this
    hence saving as xls
    What does this mean...what are you telling me?

    Give me a screen shot of the Folder (as it exists NOW) from which you're running this.

    Then, please post the files you're actually using...five of them. The Total File and four Time-sheet files. INCLUDE the code.

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

    Re: Consolidate workbooks

    Hi John, attached is what you have requested.

    By the xls reply, i meant - i saved the file in xls JUST TO SHOW YOU what the total is looking like, that's all.

    The 5 files attached are all with code

    Thanks
    Attached Files Attached Files

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

    Re: Consolidate workbooks

    And here is the folder pic
    Attached Images Attached Images

  35. #35
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786

    The Time Sheet files you sent me are just VERY SLIGHTLY different than the ones I was using. I've modified the code to accommodate both.
    mine.jpg
    yours.jpg
    Let me know if this resolves the issue.
    Attached Files Attached Files

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

    Re: Consolidate workbooks

    YUp that's brilliant and it works great.

    Do you know why the format (borders) for client 9 is different to the others, here: format.jpg

    Also, would you happen to know the formula to make the percentage divide over the number of employees/sheets?

    For example, have a look at K13 and the formula is =I13/40. But as there are FOUR sheets, that should be =I13/40/4.

    Can this be incorporated in the codes? ie divide by the number of sheets?

    Finally, did you have any luck with the pivot?

    Thanks again for all your help. You have truly outdone yourself and i cannot thank you enough... THANKS

  37. #37
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    For this
    ie divide by the number of sheets?
    Replace the Cleanup code with this
    Please Login or Register  to view this content.
    I'll look at this
    the format (borders) for client 9 is different
    and I'm still struggling with the Pivot Table.

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

    Re: Consolidate workbooks

    Cheers John, works very well. Just the pivot now if you can. All the best

  39. #39
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    See if the Pivot Table in the attached satisfies.
    Attached Files Attached Files

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

    Re: Consolidate workbooks

    Oh you genius, you... looks very good. I'll play with real data tomorrow.

    A couple of little niggles

    1. Can he data tab be hidden
    2. After pressing the run me, could the Total sheet be the first visible sheet instead of pivot
    3. The format i showed you earlier.

    But i must say, i am really thankful for your help on this.

  41. #41
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    To what are you referring here...refresh my memory
    3. The format i showed you earlier.

  42. #42
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    To do this
    1. Can he data tab be hidden
    2. After pressing the run me, could the Total sheet be the first visible sheet instead of pivot
    Add these two lines of code to Module1, Open_My_Files at the bottom of the procedure
    Please Login or Register  to view this content.

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

    Re: Consolidate workbooks

    John - This is what we talked about:

    Do you know why the format (borders) for client 9 is different to the others, and you said you will look at it

  44. #44
    Forum Expert 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,749

    Re: Help - need to consolidate workbooks

    Hi yus786
    Ha...sorta lost that in the shuffle...I'll look at it.

  45. #45
    Forum Expert 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,749

    Re: Help - need to consolidate workbooks

    Hi yus786
    To fix this
    the format (borders) for client 9 is different to the others
    In Module4, Cleanup add these lines of code at the bottom of the procedure
    Please Login or Register  to view this content.

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

    Re: Consolidate workbooks

    Perfecto John, top notch.

    I'll play with proper data tomorrow and report back

    Have you ever created a world clock for anybody in excel? Or have you ever come across one?

    We deal with clients from all over the world and would like to have a spreadsheet where say i would enter the UK time and that would give me dates for other countries too like US, India etc?

  47. #47
    Forum Expert 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,749

    Re: Help - need to consolidate workbooks

    Hi yus786

    No, haven't worked at all with Clocks in VBA. I'm certain someone on the Forum has. I'd suggest you create a new Thread for this issue...I'm sure you'll get some help. If you're of a mind, PM me with a reference to the Thread...I'd like to follow it.

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

    Re: Consolidate workbooks

    Hi John

    I'm trying to clean this up and ideally would like to give all employees the attached. As you can see, it only has one column.

    I would then like them to 'insert' another column IF they are working on another client.

    However when i do the standard insert column in D, it doesn't copy the formula over for the totals...

    Is there anyway a button could be created that would create a new column and keep the formulas intact?

    So an employee working on say 4 clients that week, would press this button 3 times to create 3 new columns

    Thanks
    Attached Files Attached Files

  49. #49
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786

    I think that's possible. I'll be traveling this afternoon until Monday but I'll see what I can do. Get back to you.

  50. #50
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    This code is in the attached. See if it does as you require.
    Please Login or Register  to view this content.
    Attached Files Attached Files

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

    Re: Consolidate workbooks

    Thanks John, i'll have a play and report back later

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

    Re: Consolidate workbooks

    Hi John

    This doesn't work. Reason is, it is saved as .xlsm and the TOTAL sheet is looking for .xls and .xlsx.

    I tried to change the following:

    If myFile Like "*.xls" Or myFile Like "*.xlsx" Then
    to

    If myFile Like "*.xlsm" Or myFile Like "*.xlsx" Then
    but that didn't work either as the total files is also a .xlsm

    I have a suggesting. What if we have another button on the Master Timesheet (Master_V1_7_YA.xlsm) which will

    1. Save the file as .xlsx or .xls and then
    2. Move the file to another location on the network (say p:\timesheet)
    3. Close the worksheet

    Is that possible? Just trying to make this as simple as possible for the users.

    THanks again and enjoy the rest of the weekend

    EDIT:

    Also John, there seems to be a problem when there's a space between the names of the timesheets. If i enter say "John 13 Feb 2012" or "John - 13 Feb 12", i get the attached error messager pointing to:

    Tgt.Names.Add Name:="_" & shtName, RefersToR1C1:="=" & shtName & "!R3C1:R10C" & LC & ""
    Attached Images Attached Images
    Last edited by yus786; 02-19-2012 at 01:09 PM.

  53. #53
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    I've worked on this in bits and spurts over the traveling weekend. The code appears to do as I believe you're looking for but I need you to test it. Again, place these two workbooks in the same folder (absent other workbooks that have .xls or .xlsx file extensions). Play with it and let me know of issues.
    Attached Files Attached Files

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

    Re: Consolidate workbooks

    Hey JOhn

    The timesheets save ok but upon running the total, i get

    myPath = ActiveWorkbook.Path & "\"
    in Open_My_Files()

  55. #55
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    I don't know what this means
    i get
    myPath = ActiveWorkbook.Path & "\"
    in Open_My_Files()
    What does that mean...you get an error? If so, what message?

    By the way, the line of code in the file I attached reads
    Please Login or Register  to view this content.

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

    Re: Consolidate workbooks

    SOrry JOhn, ignore me. Works great so far

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

    Re: Consolidate workbooks

    Hi John

    Can you give me a code to DELETE the columns? Say someone inserts a client and then doesn't put any hours in, i would like that column deleted and dont want it going to the total sheet.

    Maybe something like if the sum of Monday to Sunday is zero then delete that column?

    Leave it with you

    Thanks again

  58. #58
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    Try the code in the attached. Any Client Column that has zero hours is deleted. Let me know of issues.
    Attached Files Attached Files

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

    Re: Consolidate workbooks

    Top notch John. Doing some more testing. One thing i have noticed is that if you ONLY have the ONE client for any ONE person - it doesn't like it.

    Let me test again and give you screen shots/files

    EDIT: Here is the screen shot of 2 timesheets with only ONE client each (same client)
    Attached Images Attached Images
    Last edited by yus786; 02-23-2012 at 09:28 AM.

  60. #60
    Forum Expert 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,749

    Re: Help - need to consolidate workbooks

    Hi yus786
    This issue
    if you ONLY have the ONE client for any ONE person - it doesn't like it.
    was caused by this fix
    Do you know why the format (borders) for client 9 is different
    I added a Special_Handling procedure to Module 4 to deal with the instance when only one client is presented. Let me know of issues.
    Attached Files Attached Files

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

    Re: Consolidate workbooks

    Cheers John. i'll test tomorrow. For info - we'll be using this from next week and we cant thank you enough. THANK YOU

    One final request. At the moment - this is done on a weekly basis.

    However, if i wish to consolidate JUST THE TOTALS from say 5 sheets, is that possible (to make a Monthly total analysis sheet)

    Example: A folder has 4 files - Week 1, Week 2, Week 3, Week 4. Each file has employee timesheets and the TOTAL.

    Now if we wish to consolidate the TOTALS from each week and make a MONTH 1 sheet - is that possible?

    So this will be done once a Month. I can ask the user to save the TOTALS worksheet on a separate folder

    Thanks John

  62. #62
    Forum Expert 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,749

    Re: Help - need to consolidate workbooks

    Hi yus786

    You design it...perhaps we can build it.
    Mock it up for me so I can see what you visualize...I'll take a look at it.

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

    Re: Consolidate workbooks

    You the man...

    Before I design that, I have a small request on the current model.

    As it stands, the timesheets will be saved in the relevant week ending folder, so WE_26_Feb, WE_04_Mar etc etc and these folders MUST have the TOTALS file in there. Which means I am going to have to copy the Total_new v3.4.xlsm‎ file on each of the folder.

    So instead, is this possible?

    The Total_new file is JUST the one file. When my manager opens this file, instead of pressing the button which consolidates, how about if a prompt appears where he would select the folder which contains ALL timesheets?

    SO for example, he would have the totals sheet on his desktop. He would open and a prompt would appear for him to select the folder. He would then BROWSE and select the folder which contains the timesheets example WE_26_Feb folder.

    Is this possible?

  64. #64
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786

    I've added a UserForm to Total_new v3.5.xlsm such that, when the User clicks the Run Me button, the UserForm is populated with all the available Weekly Time Sheet Folders. The User selects a weekly folder and clicks Proceed.

    You'll need to change the indicated UserForm line of code to the Path and Folder Name where you've stored the weekly folders. Let me know of issues.
    Please Login or Register  to view this content.
    Attached Files Attached Files

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

    Re: Consolidate workbooks

    Thanks John, have you added the code to the attached sheet, or should i being that?

    Cheers

    EDIT: Sorry ignore me, found it
    Last edited by yus786; 02-24-2012 at 03:13 PM.

  66. #66
    Registered User
    Join Date
    02-25-2012
    Location
    New York, USA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Employee list( new hire)

    Greetings community!

    I just joined the forum and hope I came to the right place for my excel challenges.

    Here is my challenge,

    I am hiring over 150 people in a week time and I already interviewed and over 40 people. The new employees will be working in six different departments, three different shift.

    Here is what I want to accomplish,

    Create a master list of new hires and create a different worksheet for each department with different shift.

    I want the formula to pick from the master new hire page and distribute them to their specific department and time in each department worksheet.

    Thank you in advance
    Yankee from .New York :D

  67. #67
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi Yankee
    Welcome to the Forum!
    Please take a look at the Forum Rules (click on the Link above). Read all the Rules but, in particular, note Rule #2.

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

    Re: Consolidate workbooks

    Hey there John. We rolled this out this week and so far so good, althought the Analysis will be done next week

    A quick question. When the total file is saved - it needs to be saved as a .xlsx as it will be email around (so without the macro)

    When i chose save as and then from file type - excel(.xls or .xlsx) - i get the attached message.

    COuld you please code me a 'save as' button where i can just click on this button and the location/browse folder will appear where the file will be saved WITHOUT the macro i.e. as a .xlsx and WITHOUT the warning message as attached below

    Is this possible?

    THanks as always
    Attached Images Attached Images

  69. #69
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    You've lost me. What is it you're trying to accomplish. All the code resides in Total File. If you save as .xlsx the code will be wiped out and the file will be worthless. Please explain.

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

    Re: Consolidate workbooks

    Sorry JOhn, let me explain.

    We need to email just the total file to Senior management. Emailing the 'total' file with the codes is blank UNLESS we also email the individual timesheets.

    So after my manager has opened the total file and this has generated the consolodated file, we need to save this as a snap shot

    So the steps:

    1. Employee complete timesheets and save in week ending folder
    2. End of week, manager opens the total file and sees ALL the consolodated data
    3. Manager needs to email the data over to senior management, so needs to save as either .xls or .xlsx

    Please try this and you will know what i mean

    Add to files into folder and open the total file, then save as .xls or .xlsx and you will see the data is still there but the codes do not work, which is fine

    Hope this makes sense

    THanks

  71. #71
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    See if the attached does as you require. Let me know of issues.
    Attached Files Attached Files

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

    Re: Consolidate workbooks

    Is this the code

    Please Login or Register  to view this content.

    I've done some work on my copy and just need the code for now

    Cheers, will test

  73. #73
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    Yes, that's the code. I don't believe any other code was changed.

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

    Re: Consolidate workbooks

    2 issues John

    1. AFter i save it, it saves it without an extension. So i clicked on the button and browsed to the location and it saved it as "file" (NOT "file.xlsx")
    2. Then i tried to open up the total file and it said "Not a valid extention, file is corrupt"

    Thanks

  75. #75
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    Show me what this screen looks like when you click the Save Button
    Save As.jpg

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

    Re: Consolidate workbooks

    Here you go

    and if i pressed on cancel rather than save - all buttons on the total excel sheet also disappeared
    Attached Images Attached Images
    Last edited by yus786; 03-01-2012 at 11:28 AM.

  77. #77
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    You'll need to post your file WITH CODE. As you can see, comparing our screen shots, we're not getting the same results.

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

    Re: Consolidate workbooks

    Here you go, this is the sheet i'm using

    The "save as" on this sheet is my own - i haven't used your on this one as it just crashes out

    Basically, if you could amend this SAVE AS to save without any errors, that would be great
    Attached Files Attached Files

  79. #79
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    This code appears to work as required on your most recent file and this issue has been resolved
    if i pressed on cancel rather than save - all buttons on the total excel sheet also disappeared
    With this code you should be presented with a screen that looks like the screen shot I posted this morning.

    Please note, you MUST not save this file in the same folder with your timesheets...it's an .xlsx file and will screw with Total if it's in the same folder. What I've done in my testing is created a sub-folder and placed this file in the sub-folder. Manage it as you wish.
    Please Login or Register  to view this content.
    One other point, I had to change a line of code in Open_My_Files in order to run the code on your most recent file. I didn't try to figure out why because we're not working with the same source files.
    Please Login or Register  to view this content.
    Let me know of issues.

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

    Re: Consolidate workbooks

    Hi John

    I have pasted your code above into a module and assigned it to a button. But all i get is the browse box with ALL files as file type at the bottom. IN other words, still not getting the file saved as .xlsx

    COuld you please add the code yourself and attached it to MY sheet here?

    THanks

  81. #81
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    Please see attached. I've included your last workbook (TotalTimesheet_V4_0.xlsm) with the save code embed. I've also attached screen shots of what you should expect to see (you WILL see)
    ALL files as file type at the bottom
    Save As.jpg

    but in the file name box you'll see "TotalTimesheet_V4_0.xlsx" and that's what you will be presented with as the currently open file

    Result.jpg.

    Let's get this working to your satisfaction then I'd recommend code to remove all code from TotalTimesheet_V4_0.xlsx before you email the file. Let me know of issues.
    Attached Files Attached Files

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

    Re: Consolidate workbooks

    Sorry John, i get an error when i open the sheet

    "Run-time error '9': Subscript out of range"

    Then when debug - it goes to here:

    Please Login or Register  to view this content.

  83. #83
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    There's something about your time sheets that's different from the one's I'm using. Please post a sample or two of your actual time sheets (modified for confidential data) so I can look at them. As you recall, I mentioned this previously and it's biting us in the butt
    One other point, I had to change a line of code in Open_My_Files in order to run the code on your most recent file. I didn't try to figure out why because we're not working with the same source files.

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

    Re: Consolidate workbooks

    Here you go, edited
    Attached Files Attached Files

  85. #85
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    Your sheet naming convention in the time sheets is different from what I was using in my time sheets thus causing the error. In the time sheets I was using, the sheet name was that of the employee. If the time sheet sheet name will ALWAYS be "Sheet1" change this line of code from
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    That's the change I previously referenced and bit me in the butt...sorry about that.

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

    Re: Consolidate workbooks

    Thanks John, i have changed the code but i have lost all my buttons on the total sheet. I have tried this now a few times by putting the sheet in different folders but no joy

    ANy idea's?

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

    Re: Consolidate workbooks

    Hi again John

    Park that total sheet for now but a more pressing request has been risen.

    Our users are asking if after pressing on the submit button (which just saves the timesheets into the relevant week ending folder), all hours are then rest to ZERO.

    For instance user 1 may have 7 clients they've been working on, totalling to 55 hours. When they press submit - a copy is sent to the relevant folder and then on their local copy - all hours are reset to zero (but keeping the clients as they were, 7 in our example above)

    IS this do-able?

    Went live today and just 1 issue (local macro issue) but it went really well. THanks again John

  88. #88
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    As I understand it, after EE1 clicks the Submit button, you want EE1's time sheet saved as EE1 in the same directory as it currently resides, clearing all hours, leaving the Client Names intact. The code in EE1 workbook should be modified to do so. Is this correct?

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

    Re: Consolidate workbooks

    Hi John, no

    If you check the EE1/EE2 sheet i attached a few posts ago, the submit button actually looks at the date and then saves the file in the year/month/week folder

    See the user will also have a local copy (most of them), as they will be entering the hours on a daily basis

    So what i want (if possible) is after the file is saved into this location (year/month/week), the local copy or even the copy that is open - to have the hours cleared

    Thanks John

  90. #90
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    Upon clicking the Submit button, the code in the attached will save a copy of the Employees Time-sheet to C:\Temp in this location (year/month/week). The original file remains open, all hours are set to 0 and the original file is saved to its original location. If you want to exit Excel at the same time, uncomment
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Let me know of issues.
    Attached Files Attached Files

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

    Re: Consolidate workbooks

    Hi John, all A OK

    I have even added some msgbox loops too, to make the users confirm.

  92. #92
    Forum Expert 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,749

    Re: Consolidate workbooks

    Did you get this resolved?

    i have changed the code but i have lost all my buttons on the total sheet. I have tried this now a few times by putting the sheet in different folders but no joy
    The Save Button works like this:
    1. It asks the User where to save the file (what folder).
    2. It removes the Buttons from the worksheet so Macros can't be inadvertently triggered.
    3. It then saves the Total Sheet as an .xlsx file
    4. The original Total.xlsm file remains intact, with Buttons, in it's original location.

    If you know the folder name (or at LEAST the Path), it can be hard coded, which would be my preference.
    I would also recommend removing all code from the .xlsx file. This can be done programmaticly. Let me know.

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

    Re: Consolidate workbooks

    Hi John, no i couldn't. So i did this and it seems fine now. I added these 2 subs

    Please Login or Register  to view this content.

  94. #94
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786
    Good for you. Just remember my admonition
    Please note, you MUST not save this file in the same folder with your timesheets...it's an .xlsx file and will screw with Total if it's in the same folder
    If this satisfies your requirements, please mark your Thread as solved. You may be beyond the window of time to do so yourself...if so, please ask a Moderator to do so.

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

    Re: Consolidate workbooks

    Thanks for your help. It's really appreciated. Not many would do what you have done and i thank you from the bottom of my heart

    I still need to design the 'new total file' what we talked about a few days back.

    At the moment, the consolidation is done over weekly timesheets. The requirement would then be to consolidate JUST the total worksheet from each weekly file and make a total.

    Example, in a folder i may put 4 or 5 weekly timesheets and upon opening this NEW total timesheet, it will take just the TOTAL worksheet from each file and consolidate

    Shall i open a new thread for this?

    Thanks John

  96. #96
    Forum Expert 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,749

    Re: Consolidate workbooks

    Hi yus786

    Glad to be of help.
    I'd forgotten about this
    I still need to design the 'new total file' what we talked about a few days back.
    and I don't know how to answer this
    Shall i open a new thread for this?
    It's a new issue that's related to your apparently solved old issue. So, that would infer (to me) that you should think about a new Thread...but...on the other hand...there's a direct relationship. I'm not a Moderator so I'm not certain how they would view this.

    The advantage of starting a new Thread is you'll probably get a lot more play...which is good. You won't get too much input from others if you continue the issue in this Tread. When I see a Thread with 95 posts I might look at it to see "What the hay is going on!!!" but I won't jump in with both feet...somebody's probably got the solution surrounded.

    So, I'll leave that to you to decide (unless a Moderator sees this conversation and has a comment). If you do start a new Thread, PM me with a link and I'll look at it. Let me know what you decide.

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

    Re: Consolidate workbooks

    Hey John, how's it going? Hope all is well.

    Been using the timesheet for weeks and now and sweet as a pie. BUT (Don't you just hate buts), my management now require a change.

    Currently we are reporting on the hours worked per project but what they now need is hours worked tasks per project per country.

    I have included an example - have a look at NewTimesheets_V1 - EE1.xls and NewTimesheets_V1 - EE2.xls
    1. The days are at the top and the new columns are Country and Tasks. I want the ADD Client button to ADD a new row and DEL Client to DELETE. The submit, i can do
    2. Sheet 2 i have left visible for you to see what's going on, but i can hide that later

    Now have a look at the total sheet - TotalV1.xlsx
    1. The first tab needs to have a consolidated total of the lot (like you did on the earlier ones)
    2. Then the next few tabs would be a copy of the individual sheets.
    3. And you can then see the pivot at the end.

    Is this possible to do?

    It's all similar to what you did earlier with the exception of moving the days to the top and adding 2 more categories.

    The purpose of this is so that we can track who's doing which project in which country and which task are they actually working on

    Appreciate your help John
    Attached Files Attached Files

  98. #98
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Consolidate workbooks

    Yus786,

    I understand you started this thread, but its more than 2 months old. How about starting a new thread? You can provide a link to this thread in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

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

    Re: Consolidate workbooks

    Will do, apologies

+ 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