How to Move a Row to Another Sheet Based on the Cell Value in Excel (2 Methods)

Dataset Overview

Often, we need to move or copy rows based on cell values to different worksheets for various purposes. The cell value can be in any column. Let’s illustrate this with a sample dataset. In the dataset below, we have multiple products listed in column C. Our goal is to move the rows containing the product Cable to another sheet.

Method 1 – Use the Filter Feature

• Click the Product header (cell C4).
• Go to Home, select Editing, choose Sort & Filter and click on Filter.

• You’ll see drop-down icons next to each header.
• Select the drop-down icon next to the Product header.
• Check the box for Cable only.
• Press OK.

• This will display only the rows containing Cable.
• Select these rows and copy them (Ctrl + C).

• Go to the desired sheet.
• Choose any cell where you want to paste the rows.
• Press Ctrl + V to paste the rows.

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

Method 2 – Move Rows Using Excel VBA

Excel VBA allows us to perform various operations. In this method, we’ll use VBA codes to move rows to another sheet based on cell value.

2.1 Deleting Original Rows

• Go to Developer and select Visual Basic.

• The VBA window will appear.
• Click Insert and select Module to open a new module window.
• 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.
• The rows with Cable will be deleted, as shown in the figure below.

• Sheet1 will display the required rows.

2.2 Keeping the Original Rows

If you want to retain the original data, follow these steps to modify the code:

• Select Developer and select Visual Basic.
• Click Insert and choose Module to open a new module window.
• Paste the following code into the module:

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

• Press F5 to run the code.
• The rows with Cable will be present in Sheet2, while the original data remains intact.

Read More: How to Move Rows in Excel Without Replacing

Download Practice Workbook

You can download the practice workbook from here:

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