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

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

It's simple to move rows in Excel. You can copy and paste a whole row if you want to and then remove the original one. Done! Moving specific rows that contain specific text, on the other hand, necessitates an advanced Excel tutorial. You must learn how to use VBA and comprehend its operation. Keep in mind that there are three parts for moving data around in Excel. You must first copy the data and then paste it in the appropriate location. After that, return to the original data and delete it. This tutorial will show you a tried-and-true method that takes a few minutes to set up and then takes care of everything for you. It'll do everything for you on its own, every time. To get things started, you don't even have to click on a button.

In most cases, I produce content in Excel by resolving users’ problem. My approach is straightforward. Find or create something that works, put it through its paces, and document the entire process so that it can be used by everyone. When it comes to advanced excel tutorials, there are two approaches. Either you simply follow the instructions and find a solution to your specific Excel issue, or you understand the code bit by bit. Having to explain every single line of code that I use is awkward for me. Just being able to decipher the crucial sections of the code required to resolve your issue is important. If you have the time, check out my Free VBA Programming Course to learn everything there is to know about Excel VBA. That being said, let's return to our original subject.

We're now confronted with not one, but two problems.

If a cell contains a specific word, then the entire row should be shifted.

Automatically move rows if certain conditions are met.

When dealing with the aforementioned issues, I'll use VBA code that has two sections. To begin, I need to write code that recognizes the names of your source and destination worksheets. After that, it'll search for the specified text in the specified column. It will then copy the entire row if it comes across that text in that particular column. When it finds an empty row, it will paste the entire row so that no data is overwritten or lost. It now goes back to the original worksheet and deletes the entire row it just copied. This will ensure that the destinations do not contain any duplicate rows.

Another section of code is written to ensure that everything happens automatically. Creating a module in Excel VBA usually necessitates running it. VBA can be launched via keyboard shortcuts, buttons, menus from the quick access toolbar, and so on. However, I'll be creating a worksheet Change Event code that runs automatically whenever anything in your worksheet changes.

This is how my vba code transfers data between sheets. Let's take a closer look at the scenario I described in the video above.

This is the scenario we'll see in the video tutorial:

There are two worksheets here: the master and the completed one. The serial number appears in Column A. The project's name appears in Column B. We have a designated individual in Column C. Finally, we have the project status in column D.

You can think of it as a project manager where you can keep track of tasks, assign them to others, and see how they're progressing. Then I want to move the entire row to the finished worksheet if in column D I write "Done".

In the event you have a different worksheet name or scenario, only four things in the code will need to be altered.

To make the code work with your worksheet, you must modify the name of the source worksheet in the code.

The destination worksheet name must be changed to match yours.

To move data to another worksheet, you must specify the name of the column in which you will write the specific word.

Last but not least, you must specify the word or condition on which your data will be moved. If you're moving data based on text, be sure to use the same text in both the column and the code because the latter is case sensitive.

 

To move rows in Excel when a certain condition is met, follow the steps outlined below. As previously stated, the code is divided into two sections, and each section must be placed in the correct location.

If you specify text in this section of the code, it will move data from one sheet to the next based on that text.

Step 1: Go to the Developers menu. If you don't see that on the ribbon please learn how to add developer tab in excel

Step 2: Select "Visual Basics"

Step 3: Add a module by clicking "insert," then "module."

Step 4: Write the Code (Now here goes the article with the code)

Step 5: Exit the Visual Basic Environment.

Once the code has been automated, the entire row will be moved automatically whenever the word "Done" is written in column D.

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

Step 2: Input the remainder of the code.

3. Exit the VBE by clicking on the "Exit" button.

Save the workbook in the.xlsm format, which is an Excel macro-enabled workbook format. If you don't save your worksheet in this format, it won't work. To run the code, click on the "enable content" button each time you open the workbook. Macro security comes as standard with Windows, but you have the option to disable it by watching this instructional video on Macro Security.