+ Reply to Thread
Page 5 of 7 FirstFirst ... 3 4 5 6 7 LastLast
Results 61 to 75 of 100

VBA to Copy set data, paste into set cells, then copy more & set into cells related 2 date

  1. #61
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    844

    Re: VBA to Copy set data, paste into set cells, then copy more & set into cells related 2

    Hi thanks!!

    Another question if i may..

    If i remove names for monday, run the macro, then add names and run for tuesday, it does not add the new names.

  2. #62
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,145

    Re: VBA to Copy set data, paste into set cells, then copy more & set into cells related 2

    Where are you adding the names? In TL1?
    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]

  3. #63
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    844

    Re: VBA to Copy set data, paste into set cells, then copy more & set into cells related 2

    Hi, yes, i have removed names from TL1, ran macro and then added names and ran again (for different days)

  4. #64
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,145

    Re: VBA to Copy set data, paste into set cells, then copy more & set into cells related 2

    Can you upload the file to show me?

  5. #65
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    844

    Re: VBA to Copy set data, paste into set cells, then copy more & set into cells related 2

    Quote Originally Posted by arlu1201 View Post
    For your understanding, there are 2 vlookups in there. One is to check the Area values for the names in column B and the 2nd is to check for new entries that are missing in column B.

    Change this code from
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Hi Arlu,
    When i change the above, i get an error "Application defined or object-defined error" with the following line in yellow.
    Please Login or Register  to view this content.
    The error i posted in post#61, i must have done something wrong, as its working now, although im not sure what i did/didnt do, but anyway, it works for now!

  6. #66
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,145

    Re: VBA to Copy set data, paste into set cells, then copy more & set into cells related 2

    It worked fine when i ran it. Can you try again? Follow the steps i told you earlier - change the date in TL1, then run the macro, remove / add some data, then change the date in TL1 and run the macro again, etc.

  7. #67
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    844

    Re: VBA to Copy set data, paste into set cells, then copy more & set into cells related 2

    Hello,

    Please see attached.

    To see the error, simply run the macro.
    All i have done on this code is change the 0 to "", ran the macro with TL1 date set as =TODAY()-2, then changed the TL1 date to =TODAY()-1 and removed 3 names.

    Regards
    Attached Files Attached Files

  8. #68
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,145

    Re: VBA to Copy set data, paste into set cells, then copy more & set into cells related 2

    Oops i am sorry. I just remembered (without opening the attachment) that the syntax had some more double quotes in there.

    So change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  9. #69
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    844

    Re: VBA to Copy set data, paste into set cells, then copy more & set into cells related 2

    ok thanks for that.
    Could you please test the sheet your end on the following;

    One set of names & areas for Monday - Run Code
    slightly ammend names but keep qty the same for Tuesday - Run Code
    Ammend names AND quantity of names for Wednesday - Run Code
    Add more names & areas for Thursday - Run Code

    Hopfefully this will show you some of the issues i see

  10. #70
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,145

    Re: VBA to Copy set data, paste into set cells, then copy more & set into cells related 2

    It will be good if you specify in which columns of the TL1 sheet you do your adding and removing. I have done it myself as well but just want to know if both of us are doing the same thing.

  11. #71
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    844

    Re: VBA to Copy set data, paste into set cells, then copy more & set into cells related 2

    Hello & Good morning

    Sheet T1 - Change only date for Week commencing AND clear data from B6 to E26

    Sheet TL1 - Cell N1 Change to =today()-3
    Rung Code
    Sheet TL1 - Columns A:C - Ammend names -> Change order and change some names
    Sheet TL1 - Column G - Change order of Area (this is not so important as in reality there will be 3 areas)
    Sheet TL1 - Cell N1 -> change to =today()-2
    Run Code
    Sheet TL1 - Columns A:C - Remove some names and change order of remaining names
    Run Code
    Sheet TL1 - Colummns A:C - Add data (A=employee number (this is random) and first, last name) & add area
    Run Code.

    Thanks alot for your time, its much appreciated!

  12. #72
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,145

    Re: VBA to Copy set data, paste into set cells, then copy more & set into cells related 2

    The error that i saw was that there are some entries in column B in sheet T1 which have a single space in them. For these rows, there are 0s showing up in the areas column. Did you find the same error?

  13. #73
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    844

    Re: VBA to Copy set data, paste into set cells, then copy more & set into cells related 2

    Yes i did
    Apologies for askign you to test but i needed a sanity check that it wasnt just something i was doing wrong!

  14. #74
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,145

    Re: VBA to Copy set data, paste into set cells, then copy more & set into cells related 2

    Updated code -
    Please Login or Register  to view this content.
    Currently if its a monday, the code clears column B in the T1 sheet. Which are the other columns that need to be cleared besides the area columns? We can automate it completely, so you do not have to clear the data manually.

    I was testing the same columns but i didnt find the blank spaces. Good i did it this time. The blanks were caused by the column F in TL1. I was using that column to calculate the last row. Instead, i used column A now.

  15. #75
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    844

    Re: VBA to Copy set data, paste into set cells, then copy more & set into cells related 2

    OK, so i am going to tempt fate here.
    The latest code you provided seems to function properly regardless of the changes i make to the TL1 tab.

    I have ammendments to make to the workbook in general, as i need to replace the made up info with the actual (names employee numbers etc) but i think i can sort these.

    Thank-you very much indeed for your time on this, i appreciate it very much indeed!
    i wont mark as soved just yet on th eoff chance i encounter more issues!

    Thankyou once again.
    Galvinpaddy

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