How to Move Row to Another Sheet Based On Cell Value in Excel?

If you’re looking for a way to move rows of data from one sheet to another depending on the value in a given cell, there’s, unfortunately, no direct feature or menu item for it on Excel.

However, there are some ways that we can suggest for you to get this done.

In this tutorial we will show you two ways to move a row to another sheet based on cell value in Excel:

• Using Excel filters
• Using a simple VBA Macro

Two Ways to Move Row to Another Sheet Based on Cell Value in Excel

There are two ways to move rows to another sheet based on a cell value.

The first method is a more manual method, that involves the use of filters to extract the rows that match a given cell value.

You can then select these extracted values, copy and manually paste them to your required worksheet.

The second method involves the use of a VBA script that automatically moves the rows to your required sheet instantly whenever it matches a given cell value.

For both methods, we will consider the following dataset:

Each row in this dataset contains details about a single client.

The last cell of each row contains the status of transactions with the client. So if a deal with a client is pending, the Status will be ‘Pending’.

Now, we want that when a deal with a given client is ‘Closed’, the client’s details (or the row containing the client’s details) is moved to a separate sheet, say Sheet2.

Let us take a look at the two ways in which we can accomplish this.

Using Filters to Move Row to another Sheet based on Cell Value in Excel

Excel filters are used to extract records that match the specified criteria.

So they can be really helpful when you want to display the matching records of your data while hiding the others.

To use filters on our sample dataset, follow the steps below:

1. Select the entire dataset.
2. Click the Data tab
3. Click on the Filter icon (under the ‘Sort and Filter’ group).

4. You will see that all the header cells now have a small downward pointing arrows. Click on the arrow next to the Status header.

5. From the dropdown that appears, make sure that only the box next to “Closed” is checked. 

6. Click OK. You will now see only the rows where the Status is  “Closed”.

7. Select all the rows in view (except the header row) and press Alt+; (Cmd+Shift+Z on a Mac) so that only the visible rows are selected.
8. Right-click and select “Copy” from the popup menu, or simply press CTRL+C on the keyboard..This will copy all the visible rows only. 
9. Select the tab corresponding to the new worksheet (Sheet2 in our example), and paste it there by pressing CTRL+V.

10. Return to your original worksheet and right click on the selected visible rows.Click on the Delete option.This will remove the visible rows from the current worksheet (Sheet1).

11.  Click on the Filter button from the Data tab once again to remove the filters.

You should now see all your data, without the rows corresponding to any “Closed” clients.

Note that this whole process is partially manual since you have to physically copy and paste the filtered out rows to the new sheet.

Moreover, the process does not happen in real-time.

So in case you add a new record where the client status is closed, you will have to repeat the entire process and manually copy the filtered rows into the other sheet.

I know this is not the best way, but it’s fast and gets the work done. Use this method if you have a dataset that doesn’t update (so you only have to do this once or a few times at best). 

Also note that in this example, I have only used the cell value in one column (the status column). You can also do this for multiple cell values. For example, move all the clients where status is closed and the region in North America. For this, you simply need to apply two filters and then copy and paste the records.

In the above example, we have copied the records to another sheet, so these records still remain in the original dataset. In case you want these to move completely (i.e., not be a part of the original dataset), instead of copy-paste, use cut-paste.

Now, if you can handle a little bit of VBA and automation, let me show another way to move rows to a separate sheet based on the cell value.

Using VBA to Move Row to another Sheet based on Cell Value in Excel

A second, and more efficient way to move a row to another sheet based on cell value is by using a simple VBA macro code.

In this section, we will provide a few lines of Excel VBA code to help you accomplish this.

We will also explain the code and how it works so that you can customize it to your own requirement.

Suppose you have a dataset shown below and you want to move all the records where the status is ‘Closed’ to another sheet.

I will cover two scenarios in this section:

• VBA code to Move a row to another sheet and delete it from the original dataset
• VBA code to Move a row to another sheet and also keep it in the original dataset

VBA Code (Move Row to Another Sheet & Delete From Original Data)

Here’s the code that we are going to use:

'VBA Code by Scott from spreadsheetplanet.com
Sub move_rows_to_another_sheet()
For Each myCell In Selection.Columns(4).Cells
If myCell.Value = "Closed" Then
myCell.EntireRow.Copy Worksheets("Sheet2").Range("A" & Rows.Count).End(3)(2)
myCell.EntireRow.Delete
End If
Next
End Sub

This code will copy the entire row corresponding to a cell value containing the word ‘Closed’’ into the worksheet named ‘Sheet2’. Also, the records where the status is closed will be deleted from the original dataset.

To run this code

If your worksheet name is something else, you can change the VBA code (change the name of the sheet in the 4th line of the code)

The moved row will be pasted right after the current last row in the destination sheet.

VBA Code (Move Row to Another Sheet & Keep in Original Data)

While the above code would move all the records whether status is closed and also delete it from the original data set, below is the code that would not delete the data from the original data set

'VBA Code by Scott from spreadsheetplanet.com
Sub move_rows_to_another_sheet()
For Each myCell In Selection.Columns(4).Cells
If myCell.Value = "Closed" Then
myCell.EntireRow.Copy Worksheets("Sheet2").Range("A" & Rows.Count).End(3)(2)
End If
Next
End Sub

The only change I have done here is deleted the following line of code:

myCell.EntireRow.Delete

Now that I have given you the VBA code for both the scenarios, let me show you where to put this code and how to run it,

Where to Put the VBA code

To use the above code, all you need to do is copy and paste it into the VBA window.

Here’s how:

1. Click the Developer option in the ribbon
2. Click on Visual Basic. Ths will open the VBA window
3. In VBA backend that opens, you will see all your project files and folders in the Project Explorer on the left side.
4. From the Insert Menu, select the Module option.

4. This will open up a new module window for your selected sheet.
5. Now you can start coding. Copy the above lines of code and paste them into the blank module window.

6. Close the VBA window.

Running the Macro

Now that your macro is ready, you can go ahead and run it.

Select the area containing your data in the original worksheet.

In our example, select the range A2:D11 of Sheet1.

Next, select Macros from the Developer menu.

This will open the Macro dialog box. Select the Macro Name: move_rows_to_another_sheet and click Run.

If you used the first code (that deletes the record that is moved), your final data would look like this:

Notice that the rows corresponding to “David Jones” and “Cierra Vega” are gone, since they had the Status=”Closed”.

The two rows that have been removed from Sheet1 should now have moved to Sheet2:

Explanation of the Code

Let us take a few minutes to understand this code so that you can customize it according to your requirement.

The code loops through each cell in column “4” (or column D) of the worksheet:

For Each myCell In Selection.Columns(4).Cells

If a cell contains the value “Closed”, then we copy the entire row corresponding to that cell into Sheet2 (in column A, and the row after the last row in the sheet).

If myCell.Value = “Closed” Then
myCell.EntireRow.Copy Sheet2.Range(“A” & Rows.Count).End(3)(2)
We then delete the copied row from Sheet1
myCell.EntireRow.Delete

Note: If you would rather just send a copy of the rows to Sheet2, without deleting them from Sheet1, then you can simply omit this line from the code.

You can go ahead and adjust this code according to your dataset. Simply replace Column number 4 (in line 2) with the column number you need to analyze and replace the value “Closed” (in line 3) with the cell value you need as your qualifier for moving rows.

You can also change the sheet name from “Sheet2” (in line 4) to the name of your destination sheet.

In this tutorial, I showed you how to use a simple Filter technique and a simple VBA macro code to move rows to another sheet based on a cell value.

While the first method is more manual, the second method uses a VBA code that makes is automatic and a lot faster.

With the VBA code, you can simply select the range of cells containing your data and have all the rows containing a particular cell value moved to a different sheet by running a macro.

Happy coding!

Other Excel tutorials you may also like:

• How to Hide Rows based on Cell Value in Excel
• How to Remove Duplicate Rows based on one Column in Excel?
• How to Hide Columns Based on Cell Value in Excel
• How to Select Rows with Specific Text in Excel
• How to Select Multiple Rows in Excel
• How to Delete Filtered Rows in Excel (with and without VBA)