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

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

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

    The range that is cleared is where i paste the raw data from my email, i then have all cells reading from that range so it is showing in the correct format :

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

    Good morning Arlu1201,

    I have been playing around with this sheet, and have come across the following.

    Monday - I put all relevant info into TL1, run macro, everything works fine. (initially i thought i could simply remove the info from TL1, and replace with new info relative to the day)
    Tue/wed/thu/fri/sat/sun - remove all info from TL1, replace with new updated info, click button, macro error as previously mentioned.
    However, if i dont remove the data, and simply paste the new data underneath, then the code works, but only for the names already in column B of T1.

    Any advice?
    thanks

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

    Good morning,

    Bump

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

    This code
    Please Login or Register  to view this content.
    which you added will need to be modified. You have to specify for which sheet you need to clear contents.

    Also the code will have to go in a different place in the code, depending on what you want the code to do.
    Last edited by arlu1201; 11-20-2012 at 09:25 AM.
    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]

  5. #50
    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 Arlu,

    On the sheet you gave me earlier in post43, open this.
    In Tab T1--
    Change date for week commencing to 20/11/12
    clear column Q & column B
    Run the macro Format_report
    change week commencing date to 19/11/1.
    Run the macro format_report again, and see the issue.

    Now, please clear the sheet (or just re-open it) and start again.
    In Tab T1--
    Change date for week commencing to 20/11/12
    clear column Q & column B
    Run the macro Format_report
    Change week commencing to 19/11/12
    Then go to TL1
    and remove a few of the names
    then run the same macro, and see the problems

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

    for now, we can ignore the additional code i added, lets work with the sheet you are familiar with

    Thanks & Regards.

    galvinpaddy

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

    When i ran it as per the steps, i realised a process flow glitch.

    When you have the date changes to 20/11, D4 on Sheet T1 shows 20, if you change the date to 19/11, D4 changes to 19 but the data does not move up one column. Its not supposed to move right?

    Ideally you would be running 19/11 first and then 20/11 right?

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

    Yeah i get you. I ran the macro and saw the error. Will work on it now.

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

    Try this updated code -
    Please Login or Register  to view this content.
    If you need to test it for prior dates, you need to change the formula in N2 of T1.

    We have the part of the code which finds which column to fill in the data and it uses the TL1 - cell N2 to do that. But we have another portion of the code which ascertains which day of the week it is - Monday or Tuesday to Friday and that uses the current date. Can we maybe standardize it to either the day's date or N2 in TL1, otherwise it may create problems for you in future?

  10. #55
    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, thanks for the updated code, i will try it shortly and feedback
    As for the date, we can standardise it however you feel provides more stability for the sheet.

    Thanks.

  11. #56
    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,
    That still does not seem to work, i have taken the sheet from post #43 and changed the code to the above, if i clear the names and areas already present and run the macro, it still doesnt run properly, it will not add the names and areas but it only adds the names ?

    Please advise.
    Perhaps i am doing something that is causing an issue here, could you possibly load the sheet from your end as it works?
    Thanks ever so much.

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

    Ok, i will need to guide you a little as to how you can run it for a date which is not today. Before that, let me standardize the date factor for you.

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

    After a lot of trial & testing, here is your updated code -
    Please Login or Register  to view this content.
    To be able to run this code for any other day other than the current day, just edit the formula in N1 in sheet TL1. For e.g. if you want to run it on a monday, just minus the required number of days like this =today()-2 will give you the date for Monday.

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

    lovely thanks very much!
    Having tested that code it appears to be working.

    A few more questions if i may?

    If i change the TL1 N1 date to =today()-2 and run the macro, then change the TL1 N1 date to =today() AND remove a few names from TL1, then re-run the macro, i am shown a zero.
    How can i modifythe code to leave the cell blank?

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

    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.

+ 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