How to Move Row to Another Sheet Based on Cell Value in Excel

Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using Excel tools and features. There are many default Excel Functions that we can use to create formulas. Many educational institutions and business companies use Excel files to store valuable data. Sometimes, we need to move rows based on particular cell values from one Excel sheet to another. In those cases, moving the rows manually is a tiresome process and also time-consuming. This article will show you 2 easy ways in Excel to Move a Row to Another Sheet Based on Cell Value.

How to Move Row to Another Sheet Based on Cell Value in Excel: 2 Easy Ways

Often we have to move or copy rows based on cell value to different worksheets for various purposes. The cell value can be in any column. To illustrate, we’ll use a sample dataset as an example. For instance, in the following dataset, we have multiple products in column C. Here, we’ll move the rows that contain the product Cable to another sheet.

1. Apply Filter Feature in Excel to Move Row to Another Sheet Based on Cell Value

In our first method, we’ll apply the Filter feature to move a row to another sheet based on cell value. Therefore, follow the steps below to perform the task.

STEPS:

• First, click the Product header (cell C4).
• Then, go to Home ➤ Editing ➤ Sort & Filter ➤ Filter.

• Consequently, you’ll see drop-down icons beside every header.
• After that, select the drop-down icon beside the Product header.
• There, check the box for Cable only.
• Press OK.

• Thus, it’ll return only the rows that contain the Cable.
• See the below picture where the filtered result is demonstrated.
• Now, select the rows and copy them by pressing the Ctrl and C keys.

• Next, go to the desired sheet.
• Select any cell where you want to paste the rows.
• Press the Ctrl and V keys.
• Hence, you’ll see the following outcome.

Read More: Move Row to Bottom in Excel If Cell Contains a Value

2. Move Row to Another Sheet Based on Cell Value with Excel VBA

Moreover, Excel VBA is an amazing program by which we can carry out numerous operations. In our last method, we’ll execute VBA codes to move rows to another sheet based on cell value. So, learn the following process.

2.1 Deleting Original

Here, we’ll move rows to another sheet and delete the original ones at the same time.

STEPS:

• Firstly, go to Developer ➤ Visual Basic.

• As a result, the VBA window will pop out.
• Subsequently, click Insert ➤ Module.
• Thus, you’ll get a Module window.
• There, paste the following code.

Sub MoveRow_DeleteOriginal()
Dim rg As Range
Dim xc As Range
Dim p As Long
Dim q As Long
Dim r As Long
p = Worksheets("VBA delete original").UsedRange.Rows.Count
q = Worksheets("Sheet1").UsedRange.Rows.Count
If q = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Sheet1").UsedRange) = 0 Then q = 0
End If
Set rg = Worksheets("VBA delete original").Range("C1:C" & p)
On Error Resume Next
Application.ScreenUpdating = False
 For r = 1 To rg.Count
 If CStr(rg(r).Value) = "Cable" Then
 rg(r).EntireRow.Copy Destination:=Worksheets("Sheet1").Range("A" & q + 1)
 rg(r).EntireRow.Delete
 If CStr(rg(r).Value) = "Cable" Then
 r = r - 1
 End If
 q = q + 1
End If
Next
Application.ScreenUpdating = True
End Sub

• Save the file and press the F5 key to run the code.
• Consequently, the rows with Cable will get deleted.
• Look at the figure below where the rows with Cable are absent.

• Lastly, in Sheet1, you’ll see the required rows.

2.2 Keeping Original

However, we can also keep the original data if we wish. Learn the process to make the changes in the code.

STEPS:

• First of all, select Developer ➤ Visual Basic.
• Then, click Insert ➤ Module.
• As a result, the Module window will appear.
• Now, copy the below code and paste it there.

Sub MoveRow_KeepOriginal()
Dim rg As Range
Dim xc As Range
Dim p As Long
Dim q As Long
Dim r As Long
p = Worksheets("VBA keep original").UsedRange.Rows.Count
q = Worksheets("Sheet2").UsedRange.Rows.Count
If q = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 0 Then q = 0
End If
Set rg = Worksheets("VBA keep original").Range("C1:C10" & p)
On Error Resume Next
Application.ScreenUpdating = False
 For r = 1 To rg.Count
 If CStr(rg(r).Value) = "Cable" Then
 rg(r).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & q + 1)
 q = q + 1
End If
Next
Application.ScreenUpdating = True
End Sub

• Next, press the F5 key to run the code.
• See the below figure where the rows with Cable are present.

• At the same time, it’ll return the rows with Cable in Sheet2.

Read More: How to Move Rows in Excel Without Replacing

Download Practice Workbook

Download the following workbook to practice by yourself.

Conclusion

Henceforth, you will be able to use the above-described methods in Excel to Move a Row to Another Sheet Based on Cell Value. Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Move Rows Up in Excel
• How to Move Rows Down in Excel
• How to Rearrange Rows in Excel
• How to Move Rows in Excel to Columns
• How to Move Every Other Row to Column in Excel

<< Go Back to Move Rows | Rows in Excel | Learn Excel