How to Automatically Move Rows to a Specific Worksheet Based on Text in Microsoft Excel

In this tutorial, you’ll be amazed to see how easy it is to move data to different worksheets based on condition. It is quick, easy and everyone can do it. I’ll be using vba to transfer data from sheet to sheet and the code is also given so that you can use it in your project.

Moving rows in Excel is easy. You can cut an entire row and then paste it where you want it. Done! But when you need to move certain rows that contain certain text, that's an advanced Excel tutorial. You need to figure out VBA and you have to understand how it works. Remember, moving data in Excel requires three parts. First you copy the data, then paste it where you need it. Then come back to the source data and delete it. And in this tutorial, I’ll be sharing an excellent proven method that requires five minutes to set up, and then you don’t need to do anything. It will automatically do the job every time and it is fully automatic. You don’t even need to click on a button to initiate the procedure.

Usually, I create content in Excel by solving different problems. My approach is simple. Find or create what works, test it and then document the entire procedure so that everyone can use it. There are two ways when it comes to advanced excel tutorials. Either you just follow the instructions and solve your specific excel problem, or you understand the code line by line. I find it awkward explaining every single step of the code I use. For me, just understanding the important parts of the code you need to solve your problem is important. When you find the time, come back and check out my Free VBA Programming course and learn everything about Excel VBA. Now with that said, lets heed back to today’s topics.

Now we have two challenges here:

Move an entire row if a cell contains a specific word.

If conditions are met, move rows automatically.

As we need to solve and combine the above two problems here, I’ll be using VBA code containing two parts. First, I need to create a code that knows your source worksheet name and your destination worksheet name. Then it will look in the specific column and find the specific text you specify. Then if it finds that specific text in that specific column, it will copy that entire row. Then it will look for the next available empty row and paste the entire row so that you don’t overwrite and lose any data. Now it returns to the source worksheet and removes the entire row it just transferred. This will make sure you don’t have any duplicate rows in the destinations.

The second part of the code is created to make sure the entire process is automatic. Usually, if you create a module in Excel VBA, you need to run it. You can run the VBA by using keyboard shortcuts, using buttons, using the menus from the ribbon etc. But where I’ll be creating a worksheet_Change_Event code so that every time something changes in your worksheet the code will run automatically.

This is how my vba code will transfer data between sheets. Now let’s understand the actual scenario I used in the above video.

In the video tutorial, our scenario is something like this:

We have two worksheets here; master and completed. In Column A, we have the serial number. In Column B, we have the project name. In Column C, we have an assigned person. Lastly, in column D, we have the project status.

Think of it as a task manager where we want to record, assign, and check the status of our project. Now I want to move the entire row to the completed worksheet if I write "Done" in column D.

Now you might have a different scenario, but there are only four things you need to change in the code if you have a different worksheet name or scenario.

You need to change the source worksheet name in the code to match your own worksheet.

You need to change the destination worksheet to match your own worksheet.

You need to specify the column name where you are going to write the specific word based on which you need to move data to your destination worksheet.

Lastly, you need to specify the word or specify the condition based on which your data will be moved. Remember, if you move data based on text, then make sure they are the same both in the column and the code, because the code is case sensitive.

Now follow the below procedure to move rows in Excel if a condition is met. Now, as I said earlier, there are two parts of the code and you need to place each code in the right place.

This part of the code will move data from one sheet to another based on your specified text.

Step 1: Click on the developer tab (Learn to add developer tab in excel ribbon)

Step 2: Click on "visual basics"

Step 3: Click "insert" and then click "module"

Step 4: Now write the code

Step 5: Exit the VBE.

Now we need to automate the code so that whenever we write the word "Done" in column D it moves the entire row automatically.

Step 1: Right-click on the source worksheet name and click on "view code."

Step 2: Paste the second part of the code.

Step 3: Exit the VBE.

Now save the worksheet in an excel macro-enabled worksheet format, which is the.xlsm format. If you don’t save your worksheet in this format, it will not work. Also, you need to click on the "enable content" button every time you open the workbook to run the code. This is a built-in macro security feature, but you can override it if you watch this detailed video on Macro Security.

Now the final part. We need to customize the code based on your own worksheet.

Change this line in the code and insert the column name based on your workbook.

If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub

Here, "D: D" is the column name. Change it according to your needs.

Also, change

Set xRg = Worksheets("Master").Range("D1:D" & A)

Where "D1:D" is the column name.

Now change the source worksheet name in these lines:

A = Worksheets("Master").UsedRange.Rows.Count

Set xRg = Worksheets("Master").Range("D1:D" & A)

In these two lines, "Master" is the source worksheet name from where you need to transfer data. Let’s change the below lines to match your destination worksheet name. That is, to which sheet will your data be moved?

B = Worksheets("Completed").UsedRange.Rows.Count

If Application.WorksheetFunction.CountA(Worksheets("Completed").UsedRange) = 0 Then B = 0

xRg(C).EntireRow.Copy Destination:=Worksheets("Completed").Range("A" & B + 1)

Now in all these three lines, you need to change the destination worksheet name, and here "Completed" is the destination worksheet name and you need to change only that.

Lastly, you need to change the specific text based on which your data will be moved to another worksheet. Change it in the below lines according to your needs. Remember, it is case sensitive.

 If CStr(xRg(C).Value) = "Done" Then

Wherever you find that above line in the code, change "Done" to your specific text and done.

If you follow the procedure correctly, you’ll be able to move data to another worksheet based on cell value automatically. Thanks for reading. Check the video tutorial if you need it. If you can support us by becoming a patron, please do so. Thanks a million for being with me.

 

follow the MS Excel tutorial