+ Reply to Thread
Page 6 of 7 FirstFirst ... 4 5 6 7 LastLast
Results 76 to 90 of 99

Consolidate workbooks

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

  2. #77
    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
    You'll need to post your file WITH CODE. As you can see, comparing our screen shots, we're not getting the same results.
    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. #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

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

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

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

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

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

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

    Re: Consolidate workbooks

    Here you go, edited
    Attached Files Attached Files

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

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

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

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

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

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

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