How Move Row to Multiple Worksheets From One Worksheet Based on Cell Value or Dropdown List In Excel
HTML-код
- Опубликовано: 20 фев 2022
- How Move Row to Multiple Worksheets from One Worksheet Based On Cell Value or Dropdown List in Excel
Moving data from one worksheet to multiple worksheets is one of the most tiresome clerical tasks ever existed. There are tons of scenarios where you'll need to move data from one sheet to several others based on different conditions. Suppose you create a task manager where you aggregate all the projects in a master worksheet and then distribute them to different sheets based on different situations. Or you have to transfer each completed project to the completed worksheet, pending projects to the pending worksheet, and ongoing projects to the ongoing worksheets. If you want to do it manually, you can do that, but that will not be suitable if you want to be efficient in your workspace. You need an automation service where you'll be able to transfer data in a blink of an eye.
In this tutorial, I'll show you how to do that. I'll be using VBA here, and I've done several tutorials like this, but this one shows how to automate the whole process. Let's follow the steps below to move rows to different worksheets based on different cell values without further ado.
Step 1: Click on the developer tab (If you don't have the developer tab on your ribbon, please check this tutorial: msexceltutorial.com/post/How-...)
Step 2: Now click on "Visual Basics."
Step 3: Select "Insert."
Step 4: Click "Module."
Step 5: Compose the code (Check this link to get the code: / 62331567 )
Now repeat steps 1 to 5 based on how many destination worksheets you have. In the video, I had three destination worksheets; that's why I had created three modules.
Step 6: Change the code in each module based on your worksheet name and specific text.
Step 7: Close the VBE.
Step 8: Right-Click on the source worksheet name and paste the automation code. (Check this link to get the code: / 62331567 )
Step 9: Change the automation part based on how many macro names you need to call and the column name where you have your specific text.
Step 10: Save the workbook in .xlsm format.
Done. Now, if you select a value from the dropdown list, it will move that entire to another worksheet you specified earlier.
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
Website: msexceltutorial.com/
Thank you very much!! It helped me a lot!
You're most welcome!
Thank You!! I'm still looking through your videos, but what if you want to move from multiple source sheets to one destination worksheet for "completed" items?
This is awesome. However, I want to COPY the data and not move it. How would I change the code to do this?
This awesome tutorial, could you please make a video about copy instead of moving?
I have a lot of videos regarding that. Please check on the channel.
Thanks!
Thanks a million.
This is a great video. Quick question.. when I put complete in my drop down it moves over to the complete sheet however it deletes a previous row in that sheet. How do I fix this?
You gain a new subscriber for this one. This is a very helpful. Just a question though, what if from pending status I need to change it complete status or cancelled status, should I just copy the automation code to the pending sheet?
If you do full automation then it will be problem. If you use button to activate the code you can do that in that way.
Would I apply the same code in the master tab to the other tabs if I want to move a pending to complete? Right now my worksheet only automatically moves them from the master tab, but not the other tabs when I update the status
hi, thanks very much for this tutorial its great, question I have is, if I delete data from the destination worksheets and then move a row from the master sheet, it doesn't paste the data to the top/first available blank cell in column A. how can I ensure the data moves to the first available empty row on the destination sheet?
Select the in-between empty row, rightclick and delete. Now it will work fine.
As well, will this work if I add a new row and select one of the 3 options from the drop down list?
Yes it will work.
Thank you for making this Video. There is small problem with your code. lets say there are 10 rows of information, if i type Completed on the last row in this case the 10th row it won't move it to Completed sheet. Can you fix this please.
It will work unless you're making a mistake. Remember the specific text here is case sensitive. Make sure its same as your code.
Hi, what if I have sheets CompanyA, CompanyB, CompanyC, CompanyD, CompanyE and Master. How can I automatically move from master to their respective sheet once I type in Completed? By the way, very nice tutorial. Hope you can help me. Thank you.
You'll have to setup multiple condition. One for company name and another for conpleted.
@@Excel10tutorial Sorry but im totally noob about this. Can you kindly give me a sample code that I can just copy and edit? Appreciate your help. Thanks a lot.
Send me an email with your workbook. I'll take a look at it. Email address is in the channel about section.
This has been useful, but I'm a true novice. Do I change range in all areas you have 'C'?
Not everywhere. Just a the lines listed below.
If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
Set xRg = Worksheets("Master").Range("C1:C" & A)
How can we make the row not disappear from the master sheet? I still want to keep it there?
You need to check this video: ruclips.net/video/MNckj0Em--k/видео.html
Hi and thank you for this information! Can you please send me the VB code so I can set it up? :-)
In the description
My code is not appearing in the completed worksheet for some reason. The code look right to me. What is it that I could have done wrong?
Try again. Follow the video carefully. If you still have problem, send me an email.
I get error with the following code?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Z As Long
Dim xVal As String
On Error Resume Next
If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Z = 1 To Target.Count
If Target(Z).Value > 0 Then
Call MoveBasedOnValue
End If
Next
Application.EnableEvents = True
End Sub
Which error did you get?