How to Automatically Move Rows to Specific Worksheet Based on Text In Microsoft Excel
HTML-код
- Опубликовано: 23 июл 2024
- In this Advanced Excel Tutorial, I'll show you an excellent way to automatically move rows to any specific worksheet based on cell value. Not only this; in the bonus part, I'll show you how you can add a dropdown menu to this and whenever you select a specific value from the dropdown list, it'll move the entire row to the particular worksheet. I've created several tutorials on moving and copying data. You can check them here:
Learn To Move An Entire Row To The Bottom Of Active Worksheet Based On Cell Value In Excel: • How To Move Entire Row...
How To Move An Entire Row To Another Worksheet Based On Cell Value In Excel: • How To Move An Entire ...
How To Copy An Entire Row To Another Worksheet Based On Cell Value In Excel: • How To Copy An Entire ...
Learn To Copy Data To the Next Empty Row Of Another Worksheet In Excel:
• How To Copy Data To Ne...
How to Copy Data From One Worksheet to Another Automatically In Excel: • How to Copy Data From ...
Now let's follow the procedure below to automatically Move Rows to a Specific worksheet based on text In Microsoft excel.
Step 1: Click on the Developer tab (How to Add Developer Tab In Excel: • How to add developer t... )
Step 2: Click on Visual Basic (Learn VBA Programming: bit.ly/excelvbacourse)
Step 3: Click "Insert" and then "Module."
Step 4: Write the code (Please subscribe and here goes the code: msexceltutorial.com/post/How-...)
Step 5: Close the VBE
Now We need to Automate This. So, Follow the instruction below:
Step 1: Right Click on the source worksheet Tab
Step 2: Click on View Code.
Step 3: Write the code (Please subscribe and here goes the code: msexceltutorial.com/post/How-...)
Step 4: Close the VBE.
Now, whenever you write a specific word in a particular column, it'll move the entire row to the specific worksheet automatically.
Now, the bonus fact. Create a dropdown menu around it.
Step 1: Create a list of the menu you want in your dropdown.
Step 2: Click On the cell where you want the dropdown menu
Step 3: Click "Data" and then "Data Validation."
Step 4: Select List from the Dropdown.
Step 5: Select the menu list you just created
Step 6: Click, "OK."
Step 7: Copy the dropdown menu throughout the column and done.
Whenever you select the specific menu from the dropdown, it'll move the entire row to the specific worksheet.
#MoveRow #VBA #Excel
Thanks for watching.
-------------------------------------------------------------------------------------------------------------
Support the channel with as low as $5
/ excel10tutorial
-------------------------------------------------------------------------------------------------------------
Please subscribe to #excel10tutorial
goo.gl/uL8fqQ
Here goes the most recent video of the channel:
bit.ly/2UngIwS
Playlists:
Advance Excel Tutorial: goo.gl/ExYy7v
Excel Tutorial for Beginners: goo.gl/UDrDcA
Excel Case: goo.gl/xiP3tv
Combine Workbook & Worksheets: bit.ly/2Tpf7DB
All About Comments in Excel: bit.ly/excelcomments
Excel VBA Programming Course: bit.ly/excelvbacourse
Social media:
Facebook: / excel10tutorial
Twitter: / excel10tutorial
Blogger: excel10tutorial.blogspot.com
Tumblr: / excel10tutorial
Instagram: / excel_10_tutorial
Hubpages: hubpages.com/@excel10tutorial
Quora: bit.ly/3bxB8JG
Hello! This will come is so useful for my workplace. I would like to utilize this for multiple sheets in one workbook. Move from current to completed. Can't wait to try this out! Thank you! I look forward to watching more of your videos!
Sounds great!
This is a very awesome video, it helped me get the start of what I'm wanting to do in my spreadsheet but I still have questions. I was able to move the data as the video explains but I'm wanting to expand it to being able to move the data to different worksheets rather than only having one option.
ruclips.net/video/XMCPsyQtR5E/видео.html
You're a legend, thank you so much, this is exactly what I wanted to do. Took a little bit of fiddling, but works perfectly now
Great to hear!
This is something we have been looking for. Subscribed and form filled. Waiting for the codes to be sent through!
Sent!
Hey man great video! Wondering how long before the code gets sent though... I'm using for a project at work! So thanks again... You're making this workbook a totally different experience!
Code sent. Please check
Finaly !!! This is something I have been looking for so many time !Thanks man for shared this info. Waiting for the codes to be sent please
Just the function I was looking for. Thank you for sharing!
You are welcome!
this is exactly what i needed ! Thank you for these tutorials. Just waiting on the code so i can try this. :)
Great! Code sent
Could you send code
This is incredibly useful.
Is there a way to move it down a row on the "Completed" sheet in order to retain the same headings there? How would you accomplish this?
This was incredibly helpful! Thank you
You're so welcome!
This is exactly what I was looking for. Just waiting for the code now. Thank you!
Code sent. Please check.
Did you receive any codes ? I'm still waiting ....
Code link is in the description. Please check No more signups.
Great tutorial!! Thank you for this and the code! :)
You're welcome!
Awesome, thank you! Worked perfectly.
Glad it helped!
Thanks for the tutorial. I've completed the form and waiting for the code. Thanks again.
Sent
Great Tutorial. Thanks for this. Awaiting code so I can try it my self.
Thanks for subscribing. Code sent. Please check.
Your videos are very helpful, thank you!
Are you able to use multiple modules on multiple sheets?
Yes you can do that. Thanks for the comment and thanks a million for subscribing.
Very good! I really found this helpful
Thankyou
Thank you for the video! it was very helpful.
Glad to hear that!
Thank you for the tutorial,
I subscribed + filled out the form and I'm now waiting for the code.
Thank you 👍
Check now
@@Excel10tutorial Thanks 👍
You're welcome. Thanks for subscribing.
Hi, thank you so much for this it’s been very helpful in my work. I wanted to see what needs to be done in order to add a new command. I’m looking to add “pending” to the status and I would like it to move to the pending worksheet. please let me know if this can just be added to the module code and if so, how can it be coded. Thank you for your insight!
I think this will do the job: ruclips.net/video/XMCPsyQtR5E/видео.html
Great! This is exactly what I need, just waiting for the code!
No need to wait. Code link is already in the description.
Thanks for the tutorial, I've subscribed and filled out the form, waiting for the code
Check now
Hi! This is awesome, is there another video where you show how to add the other drop down options? Say I have 3 more sheets I need them to separate in?
Hai,
Thank you,It was a wonderful Video.....,
Is it possible to not to remove/Delete the data from the Source work sheet even though it gets appears on the destination work sheet...
thank you for this - exactly what I was looking for. Have subscribed and waiting on the code so that I can give it a go. cheers
Awesome, thank you! Code sent. Please check.
@@Excel10tutorial sadly I don't seem to have received it 😕
Code sent. Please check
@@Excel10tutorial I have checked that I filled out the form with correct email address and have checked my spam - I still have not received the code
I've sent codes to every signups. If you still didn't get it yet. Please send me an email or signup again.
Thanks amazing video! just completed the form. waiting for the code :)
Code sent. Please check
This has been so helpful and I’ve finally started to make some headway with what I’ve been trying to achieve. Is it possible to do this when you have multiple ‘master’ sheets and one destination sheet? Thanks so much
Yes you can! But with a bit different approach.
Hello! Thank you for the tutorial!
I subscribed and am waiting for the code.
Thank you!
Sent
Thank you so much for this, has change my spreadsheets ! Just wondering how I could have two drop down options going to the same worksheet?
Create two macro for same destination worksheet. Just change the text by which you want to move. Also you can check this tutorial for inspiration: ruclips.net/video/XMCPsyQtR5E/видео.html
Thank you for the tutorial, it relaly help me.
I subscribed and am waiting for the code.
Thank you!
Code Sent to everyone who signed up using the link in the description.
@@Excel10tutorial Done submitting the form. Thank you! Will wait for the code.
Sent.
Thank you so much, this is so helpful. Just wondering if it still works if I will move rows from 2 to 4 worksheets into the completed sheet.
Yes, absolutely
Thanks so much for this video and the code, it's extremely useful! Is it possible to get the row to move back from the completed worksheet if the wrong drop down option is selected by accident? I tried inserting the same code but changing the 'Master' and 'Completed' around - however, the code doesn't seem to work. Do you know if I'm doing something wrong? Sorry if stupid question, I' completely new to this!
You can get ideas from this video. ruclips.net/video/XMCPsyQtR5E/видео.html
Hi, I just recently subscribed and waiting for the code. Thanks!
Code sent. Please check your email.
Hey thank you for this! very helpful - running into a bit of a problem - when I write 'completed' which is my version for 'done' in this code, it does move to the new sheet however it appears at row 6, and if i try to do another row, it replaces the one done before? if you could help me out that would be great!!
If you can't set it up send me the workbook in .xlsx format, I'll look into it. Email address is in the channel about section.
Great tutorial and very practical to keep tabs on projects! How can I source this code, could you share it? My macro is missing something.
In the description
THANK YOU!!!!!!!!!
Most welcome. Please subscribe and be with us.
Thanks!
You're welcome.
Hi, thanks for this. its works beautifully! i do have a question though. if I wanted to move a completed (which works fine) but I also wanted to move a *cancelled*. how would I do this. i want to move from he same dropdown. to the same workbook page. I've tried adding another field but I'm getting something wrong as it crashes.
would it be possible if you can create a quick video about creating multiple moves to a workbook page from the same drop down. or another field :)
thanks a lot!
Check this: ruclips.net/video/XMCPsyQtR5E/видео.html
This is exactly what I need! Done submitting the form. Will wait for the code :)
Sent!
thanks alot for this - i've signed up and awaiting the code.
Sent
Hello! Great tutorial - can you help me with how to stack these VBA’s? I want to have two drop down options and each drop down moves the row to a different tab:
Eg:
- ‘Completed’ moved from “DATA” tab to “COMPLETED” tab
- ‘Furt’ moved from “DATA” tab to “FURT” tab
I can get one at a time to work but not multiple modules
Yes you can! For that you need to create another module for the second worksheet. Then you need to call those module inside the worksheet change event. Now if you find this hard send me an email with your workbook and instructions. I'll take a look. Email address is in the channel about section. Thanks and please subscribe.
Thanks for this, the code is working perfectly. However, I was wondering if it is possible to amend the code so that instead of deleting the row you only clear the contents - and you only clear the contents from specific cells in the row, not the entire row. Can you help?
Use these: ruclips.net/video/p-GicbMp994/видео.html
And then ruclips.net/video/Q0rlo-mzZu0/видео.html
Subscribed and signed up the form
Code sent. Please check
you are a legend! thank you so much!!
I practiced a couple of times just to see if it works, and now when row is moved, it moves it multiple rows down... is it possible to have the system fill up an empty row as close to the start / top of the spreadsheet?
Delete those empty row once. After that it won't miss.
@@Excel10tutorial thank you!!!!
You're welcome. Don't forget to subscribe.
Great video! I too am using this for a project at work with a bit of a deadline. Could you please send that over? Thanks again for your help!
Code link is aready in the description. Please check.
subscribed and Filled out the form just waiting for the code. thank you!
Code sent to everyone. Please check email.
Did you ever get the code?
Yep. Everybody does. Sometimes it takes time because i sent to to everyone manually. Sorry for the trouble.
This was really helpful thanks. I seem to have an issue though, I can move the data from sheet 1 but it doesn’t appear anywhere in sheet 2.
I get an error for the xRg(C).Entirerow. Copydestination:=worksheets(“complete”).range(“A”&b+1)
It doesn’t let me in : followed by =, I can only have one or the other so I’m assuming this might be my issue?
Send me an email. I'll take a look
Very deltailed and organized tutorial. Question, how would I modify the code if I wanted to move the row for more than key word eg. For "done" or "pending"
It is possible. Check this out: ruclips.net/video/XMCPsyQtR5E/видео.html
Awesome video! I do need help with how to add multiple keywords to run the algorithm. I am using a drop down menu and want to have the row automatically move if any of the options within that list are selected. How do I do this? I am trying to have my team input their initials in place for the "done" in your video if that helps with my end goal.
Do you want to move data to different worksheet for different keyword? If so, here is how: ruclips.net/video/XMCPsyQtR5E/видео.html
This is awesome, thank you!! Does it work for Tables as well or does the code need to change for that?
it doesn't work with table.
Thanks a lot, I being newbie trying to do the same from watching different youtubers, but always missed something. After following your video, it is working perfectly.
Glad I could help!
If you have multiple sheets that you build using a templated sheet, is there a part in the code for "module" that you can change so the Move to Sheet function works universally in the workbook?
Please explain your need.
Great video with a practical application that I can use for a work project. Where can I find the code?
In the description
Is there a way let suppose there are 5 sheets and as I type the name of that sheet that entire row data gets copied to that particular name sheet
This looks great, but do you have a place where the VBA script is listed? Thanks!
In the description
Thank you so much! Great video and I have signed up but I have not yet received the codes.
Sent!
Hi This is really awesome.
Can u post a video on how to write this code?.
This would really help me
I will in the future.
Thanks. A really easy to understand guide. Do you have a link for the code?
Sure. Code link is in the description.
Great videos. I'm working on a spreadsheet from columns A to H, H being the done column, how do I go about getting the code so when I write done it moves to new spreadsheet.
Have done Patron, found the code, but still can't get it to work. Excellent videos.
Thanks a million for the support on patreon. Please check this article, I've detailed instruction there. Hopefully your questioned will be answered there. If you still have problem contact me through email. I'll setup your workbook. msexceltutorial.com/post/How-to-Automatically-Move-Rows-to-a-Specific-Worksheet-Based-on-Text-in-Microsoft-Excel
Thank you so much for sending the code. I've to use the codes for multiple worksheets but I'm getting the following message: Compile error: Ambiguous name detected: MoveBasedOnValue. Could you advise me on what this means and what I'm doing wrong? Thank you in advance
You are using the same code for multiple times. But it doesn't work like that. If you want to do this multiple times you need to create different modules for different sheets and then call those macro from worksheet change event. Send me an email i'll set it up for you.
Great video, exactly what i was looking for. Do you think itd be possible for you to send me an alternative link to the code since the link in the website in the description isnt working for some reason. thankyou in advance
Are you still having trouble getting the code?
Great job on the guide. Total noob here, going to give it a go once the codes get sent. Thank you in advance 👍
Code sent. Please check.
Hi, I just recently subscribed and waiting for the code. Thanks
Check now
Thank you so much for this! It worked great but then when I reopened the spreadsheet the next time, it would not work at all. Any idea why?
You need to save the workbook as excel macro enabled workbook. Which is .xlsm format
If i were to do this again to go from the other oneback to the original location will it work obviously changing some of the values out.
Just change the destination worksheet to source worksheet and vise versa.
Hi there, thank you so much for this. I got this working beit when coming back from leave suddenly when changing to completed the line disappears and doesn't show up in the other sheet. What can I do?
Save the file in .xlsm format which is excel macro enabled workbook
Greetings, I got it to work with the manual process but ran into an issue after adding the Private Sub... to the Master sheet, with this error Compile error: Block If without End If. When this error came up, the following texts are highlighted in yellow "Private Sub Worksheet_Change(ByVal Target as Range). Please let me know what you think. Thanks.
Copy the code again, You missed something while copying.
I got the same error and came looking in the comments to find a solution. I managed to figure it out. Add "END IF" under the line below.
If Intersect(Target, Range("N:N")) Is Nothing Then
End If
Im no expert but I think it was because if there is an "IF" statement you need an "END IF" afterwards. It worked for me.
Danke!
Thanks a million for your support.
This is exactly what I needed after searching high and low. May I please have the code? Thank you so much :)
Code link is in the description.
Thank you for posting this. I am trying to make a simple task list that moves completed tasks to the completed page. My sheets are named the same as yours and using 'Done' as the trigger in column D. Copy and pasted your code to the Master sheet and the module. My problem is that it deletes it from the master sheet but does not put it into the completed sheet. Any help would be appreciated. Would headings on the completed sheet cause problems?
I just realized your code is working! Many thanks! It was just adding to row 794! Apparently, the formatting of the blank worksheet with grid lines down to row 793 caused my problem. Just updating this in case anyone else does not see the added rows on their screen.
I'm glad you figured it out. Thanks for letting me know. Also big thanks for subscribing.
This is great! I got it to work on my spreadsheet. I do have a question though. Let's say I marked something as Done and it moves the row to the Done Worksheet but I changed my mind and don't want it to be Done yet. How do I get it to move back to my original worksheet if I change it back to In Progress?
Just think that sheet as your master sheet setup this code again on that sheet.. you'll need to change the procedure name and it will work.
Hello! Thank you for the guide, i was wondering if i could have multiple criteria that sends the row to the other sheet as long as one of them matches? for example, either “Done” , “Ok”, “Good to go” will allow the macro to work?
Here is how: ruclips.net/video/XMCPsyQtR5E/видео.html
Thanks! Could there possibly be a shorter way? I have like 1000 eligible criteria that I want the VBA to recognize and move the respective rows to sheet 1 and then another 1000 eligible criteria for sheet 2 😂
That would lead excel to crash.
This is very helpful! Thank you. May I get the code please?
Code sent. Please check.
HI - is it possible for the VBA to look for 2 criteria? For example, using your spreadsheet, If column C contains "Jackie Hood" AND column D contains "Completed" then move the entire row
Is that possible?
Yes. Sent the solution through email. Please check and share your experience. Thanks.
This is just what I was looking for. Where can I get the code? Thank you so much!
In the description
Subscribed and waiting for the code. Thanks you
Check the link in the video description.
Thanks for tutorial, subscribed and form completed. Waiting for code.
Sent
Subscribed - awaiting code. thanks.
Sent
Hi! Thank you so much. I followed this... do you have any idea why I keep getting the error message "Compile error: Sub or Function Not Defined."
Copy the code carefully. Make sure no line is missing.
Subscribed and waiting for code thanks!
Code sent to everyone who signed up. If you haven't already please signup using the link in the description.
Thank you very much for the vid. Very well explained.
The first one works perfect. The lines which contain "Done" are sent to the other sheet.
Unfortunately the second code doesn't work for me. I copied it exactly but the second macro isn't called when enter "Done".
Did you save the workbook in .xlsm format?
@@Excel10tutorial hi, yes. The first one works so VBA is enabled. Only the macro under the worksheet isn't triggered when I enter "Done" Kind regards.
If its not the spelling send the workbook to me. I'll take a look.
This was super helpful. Please let me know once you send the code
Sent. Please check.
Wow, supertutorial! Thx very insightful
Glad you liked it! Thanks for subscribing. Please share the video.
great video! I tried to do it but seems like there something I did wrong. The code Sub MoveBasedOnValue() is highlighted with yellow. Could you help?
Please try again on a new workbook and let me know if you still have a problem.
Thanks for this tutoriel, it will be very helpfull for me. May i have the code please? Thanks in advance
In the description
I have a sheet where I would like to take the data from columns A to U and copy it to sheet 2, based on whether or not my column W says "Email". How do I account for this many columns in my script? And will this work if I have the same column headers in sheet 2?
This code move the entire row. Also Same header is required for this task.
Great video and very helpful! Are you able to send the code for this function?
Code is linked in the discription. Please check.
Is it possible to create a second row move, into a different tab, based on another project status, eg On going.
Yes, absolutely. You need to change the module name for that and call that module in the code. If you find it hard, send me an email with your project. i'll look into it. Email address is in the channel about section.
Awesome video. I found it easy to follow. May I have the code please? Not sure if it's available still. Thanks!
I found it! :) And it worked. Took me 20 minutes (my first time ever using codes on Excel)!
In the description
Hi subscribed very helpful requested code at your convenience..
Everyone who signed up using the link in the description has got the code. If you haven't done that already, please do. Thanks for subscribing.
Hi I subscribed and waiting on code. Thank you!
Sent. Please check the promotion tab and the spam folder in case the mail is not on the inbox.
Hi, do you have tutorial for google sheet doing the same- move rows
I don't have anything on google sheet. sorry.
Hi! Subscribed and also waiting for the code. Thank you!
Thanks for subscribing. Code sent. Please check.
Thank you!
You're welcome.
Is there a way of reversing the action, say if you 'Complete' a cell on the Master sheet and it moves to the 'Completed' sheet. If I then changed the cell to 'Open', is there a way of it automatically going back to the 'Master' sheet?
For that you need to configure each sheet.
Hi .... just subscribed
Waiting for code
Thank you for the help
Code sent to everyone who signed up using the link in the description. If you have done that already then check your email.
Hi, I know its too late, but i would like to know if I can do this with an entire table instead of a row. Maybe in the code i need to type "EntireTable" where it saids "EntireRow"?
It doesn't work that way.
What would the code be for the Completed tab to be able to move it back to the master if we changed the status?
For that you have to create another set of code. Assuming the Completed tab as your master worksheet and Master as your destination worksheet. Just change the procedure name in the second set.
Hey great video. Would love to get the code to use for my team
Check video description and Please subscribe and share.
Hi Excel 10 tutorial,
Please teach me how to reverse. Example when I will make the status done and it will go to Completed sheet. How about making it back to Master sheet?
There is no ctrl + z if you are using vba. If you want to get back to master sheet either you do it manually or set a code in the destination page. Just reverse. Just set up the code as if master worksheet is your destination worksheet.
Hi, this is just what I am looking for. Would it be possible to get the code please?
Code link is in the description.