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
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.
In which column on worksheet A would you enter "Complete"?
BSB
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.
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.
Hi. Thanks for the quick reply.
I would be column AA.
Thanks!
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
Hi Naveed,
Thanks for your help! Please see attached file.
Thanks!
Amy
Hello Amy,
Try the following code in a copy of your actual work book first:-
The code does as you would like and transfers data from sheet1 to sheet2 ("Complete") based on the status "Complete" in Column AA.Please Login or Register to view this content.
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:-
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).Please Login or Register to view this content.
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
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
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:-
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.Please Login or Register to view this content.
The following line of code:-
takes you directly to sheet 2 after each transfer. It could become quite annoying so, if it does, just delete the line of code.Please Login or Register to view this content.
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.
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.
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:-
As you can see, I've added the PasteSpecial function so that only values are transferred to sheet 2.Please Login or Register to view this content.
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.
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!
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
Hello Amy,
That's excellent! I'm glad that I was able to help.
Nothing quite like a temperamental computer!
Cheerio,
vcoolio.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks