+ Reply to Thread
Page 4 of 7 FirstFirst ... 2 3 4 5 6 ... LastLast
Results 46 to 60 of 99

Consolidate workbooks

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

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

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

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

    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.

  5. #50
    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 is in the attached. See if it does as you require.
    Please Login or Register  to view this content.
    Attached Files Attached Files

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

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

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

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

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

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

    Re: Consolidate workbooks

    SOrry JOhn, ignore me. Works great so far

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

  13. #58
    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
    Try the code in the attached. Any Client Column that has zero hours is deleted. Let me know of issues.
    Attached Files Attached Files

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

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

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