+ Reply to Thread
Results 1 to 100 of 100

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

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

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

    Afternoon happy campers,

    Attached is the workbook i am working on.

    Tab - T1 is blank
    Tab - T2 shows the desired result after macro is run
    Tab - TL1 shows the data as i will use it, and explains (hopefully easy to understand) what i would like to happen with each bit
    Tab - TL2 shows the raw data (A:G) as it will be pasted into the sheet.

    The command buttons on TL1 & TL2 need to be linked -> When Macro in TL1 is run it needs to feed into T1 & the same for TL2 into T2.

    Apologies if the descriptions on each bit appear sarcastic, not intended, i just have an uncanny ability to partially explain things!

    Kind regards,
    galvinpaddy
    Attached Files Attached Files

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

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

    You can have just 1 macro that feeds both sheets. Or is there a chance of running them both independently, so you want the macro buttons to be independent?
    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. #3
    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 and thanks for your reply!

    The sheet will be used by 2 different people on 2 different teams (AM & PM) so ideally i would like the codes & buttons to be seperate from each other, i assume the changes between the 2 would only be the sheet name the data is moved to so i should be able to sort that, if you could provide one code for me that would be much appreciated!

    regards, galvinpaddy

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

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

    So before re-loading the data, should only column B be cleared or should the corresponding area column for the previous day be cleared too?

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

    HHHmmmm, you have just highlighted a potential flaw already.
    I need the names and areas for each day to remain, and only add names to the list, as i need to avoid having duplicates and missing names.

    Ok, so, can the formula work by;

    Monday -> add all names into T1 from TL1, and the corresponding area.
    Then for every other day of the week, if the name exists and is due to arrive -> add the expected area, if not then to leave the area blank, but not delete or overwrite the next unique name.
    Does that make sense.

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

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

    In that case, a vlookup formula should help you out. But for the vlookup to work properly, we need to have the names (column J) to the left of column G.

    Can i use column F in this case? If it wont be possible for you to change your layout, then i can use column F as a temporary column and then clear it once the lookup is done.

  7. #7
    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, that should be ok as i can ammend the source layout

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

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

    Monday -> add all names into T1 from TL1, and the corresponding area.
    Then for every other day of the week, if the name exists and is due to arrive -> add the expected area, if not then to leave the area blank, but not delete or overwrite the next unique name.
    This is fine. However, what if the 1st monday is 1st June and the above steps are done and then comes 8th June. As per your above description, since its a Monday, should the data be cleared and fresh names input? Or should we just continue as if its any other day of the week?

  9. #9
    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, every Monday I would clear the data and start fresh.

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

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

    So that the macro can clear the data, should it check if the day of the week of the day you are running the macro is a Monday and then decide to clear the contents or not?

    Or should it check if the day of the week of cell B3 of the T1 sheet is a monday and then clear?

  11. #11
    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 and thanks to for the reply.

    Can we try both and see which works best?

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

    Is there anything i can do to help?

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

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

    Let me work out the code for you with option1 above. Then i can provide you the alternate code for option2. Its only a small bit of the code that will change not the whole thing.

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

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

    Thinking about it again, while working on the code, i realised that we cannot use cell B3 of the T1 sheet. If we do, the macro will just run once.

    We will have to use today's date itself or use the date which is stored in cell N1 sheet.

  15. #15
    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 Arlu1201,
    The date does not need to be in B3, i had only put it there whilst i was trying to explain it to myself before realising i couldnt figure it out!!

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

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

    So can we use today's date or the date in N1 of sheet TL1?

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

    Which ever would work better.
    The date in B3 would only be there as we would print the sheet each day for the teams to use.

    This sheet is to stop people writing their names multiple times and also so we can read the names

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

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

    Try this code. I had to work out the logic a bit to get this working correctly, so it took time. Put it in a standard module.
    Please Login or Register  to view this content.

  19. #19
    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, please see attached.
    I have added the code into a module as requested, when i run i receive an error - Compile Error: Variable not defined.

    Regards
    Attached Files Attached Files

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

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

    Which line does it highlight?

    Edit: I got it. Sorry i missed out on this. Add this line to the end of your DIM statements at the top of the macro -

    Dim lastrow as long
    Last edited by arlu1201; 11-01-2012 at 08:23 AM.

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

    er.png

    When it opens the VB screen, it shows 'Sub format_report()' in yellow and 'Lastrow =' in blue

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

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

    I got it. Sorry i missed out on this. Add this line to the end of your DIM statements at the top of the macro -

    Dim lastrow as long

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

    Now when i run the code, the only result i see is that T1 range B6:B49 is highlighted, it also deletes the info in Cell A6, (in sheet T1)

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

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

    But i am not touching column A in the T1 file. Dont you get the output in the Area column for the date and the names in column B?

    Can you attach the file?

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

    Attached
    Attached Files Attached Files

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

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

    Since its taking today's date, and its a Thursday, you need to ensure that there is data already in column B of T1. Then run the macro.

  27. #27
    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,
    I have added names into Column B and areas into Column E, H & J, but the macro deletes the areas in Column E and also replaces part of the names in COlumn B with the wrong info (macro appears to be taking data from Column F in TL2, which is not whats required.)

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

    Update - OK, so the macro will delete all info in Column A, and will start almost at the end of Column B, overwrite whatever data is present and replace it with the info from Column F on tab TL1.

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

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

    It will not overwrite the data in column B if its not a monday.

  30. #30
    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, it does overwrite the data in column B, not all of it, just some of it.
    It also does not add the area into the required field.
    On T1, i have added names from B6 down to B45, all the names currently in tab TL1 and some extra ones, when i run the macro, it should see todays date, and add the area for each name into Column N, but, it does not.
    It deletes the data in Column A (more specifically, ANY cell in Column A that contains data), and will then only add more names in Column B, it will also not add the area in Column N (which is the area for that day)

    Regards.

  31. #31
    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, please see attached.
    This shows the names in B, Times and Areas in Mon, Tue & Wed.
    The parts where i should see data after running the macro are in red.
    The parts the Macro has affected are in blue.
    The parts the macro highlights once it is finished are highlighted in orange.

    Hopefully that might help explain the issue

    Thank-you alot for your time & patience so far

    Regards.
    Attached Files Attached Files

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

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

    Maybe i can explain what i have done, so you understand the flow.

    It checks the day of the week - if its a monday, it clears the data in column B and enters the area figures in the corresponding column. (I have not added in the code to clear all the "Area" columns. Let me know if i need to do that.
    If its not a monday, it does a vlookup to compare column B of the T1 sheet with column F of TL1 to find missing names. (As i mentioned, i will be using column F and not J). It copies the missing names into column B and then does a vlookup to copy the area figures into the corresponding column.

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

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

    Check my reply above. Also, what should go into columns L & M? You didnt mention anything.

  34. #34
    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 your time.

    First off - L:M -> ignore that (my mistake whilst working at same time), this is a column manually entered by staff and then updated by me later. in Column N i need to see the area relating to each name in Column B, Taken from the data in TL1.
    Data in TL1 is replaced everyday when we receive the full list of staff and areas they are working in.
    In my attachment above, you can see where one issues occurs, column A has data removed, and column B has nothing added but is highlighted. Column N needs the area for each name in column B that is taken from the range in TL1.

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

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

    Did you check the process flow i laid out in post 32? Is that fine with you? I couldnt find another way of getting this done.

  36. #36
    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, yes, that should be fine. However, will the sheet replace all formulas with values only? (if so good )

    And thanks again for your time

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

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

    Yes its a pastespecial values.

    If you run it again, are you still facing the issues you outlined in post 31?

  38. #38
    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
    Can i ask, have you tried this in Excel yourself? the errors on my end will always show as the coding is set to do the same thing.

    I have tried it again on a sheet that contains info in B, Mon, Tue, Wed and the relative area columns, and no data is added into todays column and the code is still deleting whats in column A and adding the wrong info into column B.

    Do you have a copy of this spreadsheet on your desktop that you could perhaps try and see the errors? if you have one but dont get errors, please post it so i can see

    regards

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

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

    Yes, i tried the code as soon as i created it. I will run it again step by step and check it out.

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

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

    The cell in N2 in sheet TL1 is in single digit format without the leading 0 while row 4 had double digits. That was causing the problem. I will post the updated code shortly.

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

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

    Updated code - Since today is friday, ensure that there is some data in column B of T1.
    Please Login or Register  to view this content.

  42. #42
    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 your reply.
    when running the macro i am presented with an error -"Run-time error '1004': Unable to get Vlookup property of the worksheet function class.
    Highlighting the following line in yellow
    Please Login or Register  to view this content.

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

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

    Keeping the line highlighted, if you hover your mouse over j and rcol, what values do you see?

    I ran it at my end and it worked fine. See attached.
    Attached Files Attached Files

  44. #44
    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, having revisited this, i run it on a monday, and it runs fine
    I will test throughout this week and get back to you!

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

    I have tried to run the code today and receive a run time error.
    I have modified the code slightly by adding
    Please Login or Register  to view this content.
    Onto the end of yours (just after the screen updating.true part.
    When i clear the area and add a new set of names & info, the run time error flashes up and highlights the following line in yello
    Please Login or Register  to view this content.
    Last edited by galvinpaddy; 11-13-2012 at 07:47 AM.

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

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

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

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

    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.

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

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

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

    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?

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

    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.

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

    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?

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

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

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

    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.

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

    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.

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

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

    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.

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

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

    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?

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

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

    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?

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

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

    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.

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

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

    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.

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

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

    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.

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

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

    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?

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

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

    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.

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

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

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

    Am glad its sorted The experience was good, as if i am creating a template of my own

  77. #77
    Forum Contributor
    Join Date
    11-05-2012
    Location
    Montreal
    MS-Off Ver
    Excel 2007
    Posts
    107

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

    Hello Arlette,

    If you don't mind can you please put the template you are creating?

    Thank you

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

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

    I didnt create any template Naja. I meant the experience felt as if i was creating a template of my own since i had to do complete process flow testing.

  79. #79
    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 Arlu,
    having had some time to play with the sheet, attached you will find my issue.
    I have removed the TL2 & T2 tabs for now, just to get the first one running -> Comments on TL1
    Attached Files Attached Files

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

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

    I am not sure if you used the updated code since i remember correcting in the exact same places as i did now -
    Please Login or Register  to view this content.

  81. #81
    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, i am using that code
    Have you tried it?
    When i run the code and using the steps above, the code duplicates names in Column B which is the problem.
    It does insert the areas correctly, just makes a mess of the names .

    Regards

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

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

    Try this updated code - did a few changes -
    Please Login or Register  to view this content.

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

    Still having issues
    I run the new code for monday, change names & date, run again, and it goes messy.
    As attached.
    Many thanks.
    Attached Files Attached Files
    Last edited by galvinpaddy; 11-28-2012 at 04:43 AM.

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

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

    What do you mean by messy? Its showing that the data is populating in the 3rd Area column for whichever names were showing in the column. And then it added new names through the vlookup.

  85. #85
    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 and thanks for your time!

    I dont believe i have been so DAFT, i really am sorry. i completely overlooked the fact that i had been adding unique names on each day. really do apologise!!

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

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

    No problem at all. Can we mark this as solved now?

  87. #87
    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,
    I will mark as solved, hopefully we can put this one to rest!
    Thank-you very much indeed for your support!

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

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

    You are welcome. It was a nice and challenging thread to work on.

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

    As mentioned previously

    There is a description on the last tab
    Attached Files Attached Files

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

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

    Will try it out and let you know shortly.

  91. #91
    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 & Happy new year to you

    Did you manage to have a quick look at the sheet?
    Thanks!
    Galvinpaddy

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

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

    Sorry, got held up. I will check into this and let you know.

  93. #93
    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 evening, politely bumped

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

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

    Can you point out the exact issue you are facing?

  95. #95
    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 Arlu!

    The best way to explain the issue is in the attachement i loaded on post #89.

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

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

    Please try this updated code -
    Please Login or Register  to view this content.

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

    I think we have the solution!!!
    There are some smaller additions i would like to add, if of course you wouldnt mind

    Massively greatfull so far !!

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

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

    Sure. Sorry dont know how i missed replying to your post.

    What are the additions?

    Also, i saw that you have 2 separate codes - 1 for AM and 1 for PM. We can try and make it into 1 code, now that it works.

  99. #99
    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
    Having 2 codes is fine, they both work spot on
    I have opened a new thread for one of the additions, having read the forum rules i cant find one that stop me from doing this, so below is link to new question.
    http://www.excelforum.com/excel-prog...html?p=3107816

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

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

    Ok great. Then we can mark this thread as solved.

+ Reply to Thread

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