+ Reply to Thread
Results 1 to 14 of 14

[SOLVED] Need to move row to bottom of another worksheet based on cell criteria

  1. #1
    Registered User
    Join Date
    03-18-2016
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    12

    [SOLVED] Need to move row to bottom of another worksheet based on cell criteria

    Hi.

    I am trying to move an entire row from worksheet A to another worksheet (B) (the next empty row in worksheet B), based on a cell= "Complete" in worksheet A.
    I have minimal experience with macros. Can someone help?

    THANKS!
    Amy
    Last edited by amymsellers; 03-30-2016 at 04:27 PM.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Need to move row to bottom of another worksheet based on cell criteria

    In which column on worksheet A would you enter "Complete"?

    BSB

  3. #3
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Need to move row to bottom of another worksheet based on cell criteria

    Hi Amy

    please find the attached sample file.

    in Data Sheet in 1st Column type "Complete" , once you type and hit enter it will automatically moved to result sheet.
    Attached Files Attached Files
    Thanks - Naveed
    -----------------------------
    If the suggestion helps you, then Click * to Add Reputation
    To Attach File: Go Advanced>>Manage Attachments>>Add Files (In Top Right Corner)>>SelectFiles>>.........Locate Your File(s)>>Upload Files>>Done (In Bottom Right)
    1. Use [code] code tags [\code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

  4. #4
    Registered User
    Join Date
    03-18-2016
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    12

    Re: Need to move row to bottom of another worksheet based on cell criteria

    Hi. Thanks for the quick reply.
    I would be column AA.
    Thanks!

  5. #5
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: Need to move row to bottom of another worksheet based on cell criteria

    Hi Amy

    based on sample data i have prepared , however if you provide sample workbook then it will be more easy to provide you perfect solution as i want to check how your data will look

  6. #6
    Registered User
    Join Date
    03-18-2016
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    12

    Re: Need to move row to bottom of another worksheet based on cell criteria

    Hi Naveed,
    Thanks for your help! Please see attached file.
    Thanks!
    Amy
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Need to move row to bottom of another worksheet based on cell criteria

    Hello Amy,

    Try the following code in a copy of your actual work book first:-


    Please Login or Register  to view this content.
    The code does as you would like and transfers data from sheet1 to sheet2 ("Complete") based on the status "Complete" in Column AA.

    Attached is my test work book based on the sample that you supplied. Click on the Transfer Data button to see the code at work.

    I've made some subtle changes in the test work book such as naming Column AA "Status" and just following some standard protocols with spelling (generally upper and lower cases). So check that the work sheet tab names are exactly the same as in the code and that the Status criteria is exactly the same as in the code (Complete).

    You will also note that the following line of code:-

    Please Login or Register  to view this content.
    has an apostrophe in front of it. This de-activates this particular line of code as I didn't know if you wanted the "used" data in sheet1 cleared after each transfer of data to sheet2. If you do, simply remove the apostrophe and the line of code will be activated. This would be a good idea otherwise you will end up with many duplicates in sheet 2 (unless, of course, you change the status criteria after each transfer of data).

    I hope that this helps.

    Cheerio,
    vcoolio.

    P.S.: The site's file uploader appears to not be working, so following is the DropBox link to my test work book:-

    https://www.dropbox.com/s/y0qeliwneu...lers.xlsm?dl=0

  8. #8
    Registered User
    Join Date
    03-18-2016
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    12

    Re: Need to move row to bottom of another worksheet based on cell criteria

    Thanks Vcoolio! This is very helpful. I did want the "used" data to be cleared so, I removed the apostrophe and, it worked just as you said it would.

    I do have one more question. Do you know if there is anyway to accomplish the same thing without having to click the button? Is there a way to automate it so that based on cell being populated w/ "Complete" in column AA/Status, the row will auto clear from "Tracking Sheet" and move to "Complete" tab?

    Thanks so much!!!!!
    Amy

  9. #9
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Need to move row to bottom of another worksheet based on cell criteria

    Hello Amy,

    There is a way of doing this. Its called a Worksheet_Change event. To do this, the code needs to be placed in the worksheet module. So, right click on the Tracking Sheet tab and select "view code" from the menu that appears. In the big white field that appears, paste the following code:-


    Please Login or Register  to view this content.
    Now, go back to the Tracking sheet and enter "Complete" in a cell in Column AA then click away (or press enter or down arrow) and the relevant row of data will be transferred to sheet 2 and will be deleted from sheet 1.

    The following line of code:-

    Please Login or Register  to view this content.
    takes you directly to sheet 2 after each transfer. It could become quite annoying so, if it does, just delete the line of code.

    You'll notice that the above code is somewhat different to the first one I supplied but do not despair, it does the same job and I could have used the first code in the work sheet module. Its just me keeping the brain active and reminding myself that there are a number of ways to go about such an exercise.

    Just leave the first code where it is. You may want to refer to it in future. Delete the button as you won't need it.

    Anyway, I hope that all is resolved for you now.

    Cheerio,
    vcoolio.

  10. #10
    Registered User
    Join Date
    03-18-2016
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    12

    Re: Need to move row to bottom of another worksheet based on cell criteria

    Hey Vcoolio.

    Thanks again for your time and expertise. Unfortunately I can't get this to work. I did as you said (copy/pasted code) after right clicking worksheet tab/view code.
    When I enter "Complete" into a cell in AA, nothing seems to be happening.
    It's probably user error but, do you have any other suggestions or things I might check?

    Please see attached (updated w/ newest code).

    THANKS!
    Amy
    Last edited by amymsellers; 03-30-2016 at 03:32 PM.

  11. #11
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Need to move row to bottom of another worksheet based on cell criteria

    Hello Amy,

    Me thinks its because you have formulae in Column AA, previously unbeknown to me, so, a minor adjustment to the code as follows should resolve it for you:-


    Please Login or Register  to view this content.
    As you can see, I've added the PasteSpecial function so that only values are transferred to sheet 2.

    Create a copy of your work book, clear out Column AA and then re-instate the formulae. Type the criterion "Dependencies" in Column N and all should work as it should. If you are happy that the copy of your work book is working as it should, then do the same in your actual work book; i.e. clear out Column AA and start again.

    The file uploader appears to be working at your end but still not here on my end so if any Moderators drop by, then please advise!

    In the meantime, following is the DropBox link to the updated test work book:-

    https://www.dropbox.com/s/62p6x7w8xk...2%29.xlsm?dl=0

    (To see it work, just overwrite the criteria "Complete" in any cell in Column AA in the test work book, even though this will delete the formula in the cell, and click away).

    It should all work as expected for you now. Let me know how you get on.

    Cheerio,
    vcoolio.
    Last edited by vcoolio; 03-23-2016 at 02:20 AM.

  12. #12
    Registered User
    Join Date
    03-18-2016
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    12

    Re: Need to move row to bottom of another worksheet based on cell criteria

    Hi Vcoolio.
    Not sure what I'm doing wrong but I haven't been able to get this to work. Thanks so much for your help.
    Thanks!

  13. #13
    Registered User
    Join Date
    03-18-2016
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    12

    Re: Need to move row to bottom of another worksheet based on cell criteria

    Hi Vcoolio.
    Please disregard my above email. It's working GREAT!
    I restarted computer and, it seems to be working fine.
    thanks so much!
    Amy

  14. #14
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,095

    Re: Need to move row to bottom of another worksheet based on cell criteria

    Hello Amy,

    That's excellent! I'm glad that I was able to help.

    Nothing quite like a temperamental computer!

    Cheerio,
    vcoolio.

+ 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. [SOLVED] Macro to move data from one worksheet to another based off of criteria;
    By KLiving in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-17-2015, 12:41 PM
  2. [SOLVED] Macro Needed - Move data to worksheet based on criteria
    By sharpyoungb in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-10-2014, 11:42 AM
  3. [SOLVED] Move Row to another worksheet based on a Yes No Criteria
    By littleliz02 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-18-2013, 01:09 AM
  4. Move data to specific worksheet based on Column A criteria.
    By forestminuet in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-07-2013, 01:44 PM
  5. Need to move data that matches criteria in cell and move to new worksheet
    By panagle in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-27-2013, 09:23 AM
  6. Move rows from worksheet to a workbook based on criteria
    By pjlau in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-18-2012, 10:45 PM
  7. move to bottom of worksheet
    By jmhultin in forum Excel General
    Replies: 3
    Last Post: 08-17-2008, 08:11 PM

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