+ Reply to Thread
Page 1 of 7 1 2 3 ... LastLast
Results 1 to 15 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,145

    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,145

    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,145

    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,145

    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,145

    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,145

    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,145

    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!!

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