+ Reply to Thread
Page 2 of 7 FirstFirst 1 2 3 4 ... LastLast
Results 16 to 30 of 99

Consolidate workbooks

  1. #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.

  2. #17
    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,750

    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.
    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. #18
    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,750

    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

  4. #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

  5. #20
    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,750

    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.

  6. #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

  7. #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.

  8. #23
    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,750

    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

  9. #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

  10. #25
    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,750

    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.

  11. #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.

  12. #27
    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,750

    Re: Consolidate workbooks

    Hi yus786

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

  13. #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

  14. #29
    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,750

    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.

  15. #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?

+ Reply to Thread
Page 2 of 7 FirstFirst 1 2 3 4 ... 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