+ Reply to Thread
Page 3 of 7 FirstFirst 1 2 3 4 5 ... LastLast
Results 31 to 45 of 100

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

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

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

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

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

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

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

    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.

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

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

    Yes its a pastespecial values.

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

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

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

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

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

  11. #41
    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 - Since today is friday, ensure that there is some data in column B of T1.
    Please Login or Register  to view this content.

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

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

    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

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

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

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