+ Reply to Thread
Page 2 of 7 FirstFirst 1 2 3 4 ... LastLast
Results 16 to 30 of 100

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

  1. #16
    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 can we use today's date or the date in N1 of sheet 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]

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

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

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

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

    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.

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

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

    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

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

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

    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?

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

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

    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.

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

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

  14. #29
    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 not overwrite the data in column B if its not a monday.

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

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