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.


Download Practice Workbook

Download the following workbook to practice by yourself.


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

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 which 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.

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

  • 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.

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

  • 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.

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

  • 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.

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

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.

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

  • 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

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

NOTE: In the code, make necessary changes. Input your original sheet name instead of ‘VBA delete original’. Then, type your new sheet name instead of Sheet1 where you’ll move the rows. Next, edit the range C1:C to meet your required column where the cell value will be. Finally, replace the Cable with your desired cell value.
  • 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.

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

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

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


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

NOTE: In the code, input your original sheet name instead of ‘VBA keep original’. Then, type your new sheet name instead of Sheet2 where you’ll move the rows. Next, edit the range C1:C10 to meet your required column where the cell value will be. Finally, replace the Cable with your desired cell value.
  • 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: Move Row/Column in Excel Without Replacing Existing Data (3 Best Ways)


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. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo