+ Reply to Thread
Page 3 of 7 FirstFirst 1 2 3 4 5 ... LastLast
Results 31 to 45 of 99

Consolidate workbooks

  1. #31
    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
    Interesting...I'll try to duplicate the issue...get back to you.
    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.

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

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

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

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

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

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

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

  9. #39
    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
    See if the Pivot Table in the attached satisfies.
    Attached Files Attached Files

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

  11. #41
    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
    To what are you referring here...refresh my memory
    3. The format i showed you earlier.

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

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

  14. #44
    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
    Ha...sorta lost that in the shuffle...I'll look at it.

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

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