+ Reply to Thread
Results 1 to 8 of 8

Code to search for items, copy to different sheet and delete them

  1. #1
    Registered User
    Join Date
    09-24-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Code to search for items, copy to different sheet and delete them

    Hi all,

    I have a workbook with one sheet (called "Pipeline") and another sheet called "Completed". The Pipeline sheet is used to keep track of all of the tasks that the team are working on, and then when the task is completed, it needs to be moved to the "Completed tab".

    I currently have managed to write some code that looks for items marked "completed" in row G of the "Pipeline" sheet, then copies them over to the "Completed" tab, and then deletes the row from the "Pipeline" sheet.

    There is one macro to copy the completed cells (called "Transfer"), and then another to delete the cells (called "Delete"). I then just have macro called "Clean" that just calls the transfer macro, and then calls the delete macro.

    However, I'm new to VBA and I'm pretty sure the code is awful as it sometimes just doesn't work. Often, there are 6 tasks marked complete, and only some of them will be copied over, but all of them will be deleted (a disaster).

    I'm just hoping if anyone could give me some tips to make the code better and more robust? Thanks!

    Transfer Macro:

    Please Login or Register  to view this content.
    Delete Macro:


    Please Login or Register  to view this content.
    Overall Macro:

    Please Login or Register  to view this content.

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Code to search for items, copy to different sheet and delete them

    Place the following macro in the worksheet code module for the "Pipeline" sheet. The macro is triggered by a change in any cell in column G so make sure that column G is the last column to be filled. When you enter "Completed" in any cell in column G, the range in that row will be copied over. Try it on a copy of your file. Please let me know if this works for you.
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    09-24-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Code to search for items, copy to different sheet and delete them

    Thanks so much for your response! Unfortunately it doesn't seem to work - nothing happens when I select col G as completed from the dropdown. The only other code in the spreadsheet is in Sheet 1 (Pipeline), which ensures that the team cannot select completed as an option without providing a completed date in column U. Does this interfere with anything?
    Please Login or Register  to view this content.

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Code to search for items, copy to different sheet and delete them

    When I tried it on a dummy sheet, it worked for me. It would be easier to find the problem if I could see your actual file. One problem is that any worksheet can have only one Worksheet_Change event so the two macros may have to be combined into one. If you could post your file, I'll be happy to have a look.

  5. #5
    Registered User
    Join Date
    09-24-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Code to search for items, copy to different sheet and delete them

    Thanks - I actually was being silly and didn't paste into the right worksheet module. I have spent some time and merged your code with my other code in post #3 and have got the below!

    One last question I had - with this code I cannot copy or delete whole rows - I get the error message "The status may not be set to complete when no completion date has been provided in Column U", and the cells are just end up blank.

    I have a feeling it's something to do with the Application.EnableEvents but not sure how to correct it. Any thoughts welcome!

    Thanks



    Please Login or Register  to view this content.

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Code to search for items, copy to different sheet and delete them

    This code seems to do what you want with one exception that I can't seem to figure out.
    Please Login or Register  to view this content.
    It works if you don't delete the copied row after it is copied. If you try to delete the row after it is copied, you get the 'Type mismatch' error and I can't figure out why. Maybe someone with more expertise than I could help out. Could I suggest you start a new thread posting the code and explaining that the error is generated when you try to delete the row. If you find a solution, could I ask you to send me a private message with the link to the solution. I am still learning VBA and I would like to see what the problem is. Many thanks.

  7. #7
    Registered User
    Join Date
    09-24-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Code to search for items, copy to different sheet and delete them

    Great, I'll do that then! Thanks for your help!

  8. #8
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Code to search for items, copy to different sheet and delete them

    Terrific! A gentle reminder to please send me a private message with the link to your new thread. I would like to follow it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA code to search from different sheet > Copy the row > Paste on different sheet.
    By qpywsqp in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-26-2013, 11:13 AM
  2. search code-date then copy name-date-time to another sheet
    By k1dr0ck in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-14-2013, 02:25 AM
  3. [SOLVED] Code to search for all hyperlinks in a column & copy them to an alternative sheet
    By matrixpom in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-12-2013, 05:35 PM
  4. [SOLVED] Looking for vba code that will search sheet for a value on a row and copy other values.
    By Justin25150 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-03-2012, 12:34 PM
  5. i want to delete duplicate items in a list using code.
    By ndm berry in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-27-2005, 10:05 AM

Tags for this Thread

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