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

If you routinely work with large datasets or need to efficiently organise your data, this lesson will show you how to automate the transfer of rows based on specific criteria. Excel is an effective tool for organising and analysing data, and by utilising its features and functionalities, we can automate time-consuming procedures to save effort and save time. For instance, it is commonly required to shift entire rows to another sheet depending on a cell value while filtering and categorising data.

Move Entire Row to Another Sheet Based on Cell Value

Here, we will first create a VBA module and then run it to complete the task. So let us see a simple process to know how you can move an entire row to another sheet based on a cell value in Excel.

Step 1

Consider an Excel sheet where the data in the sheet is similar to the below image.

First, right-click on the sheet name and select View code to open the VBA application.

Right Click > View Code.

Step 2

Then click on Insert and select Module, then copy the below code into the text box.

Insert > Module > Copy.

Code

Sub Cheezy()
   Dim xRg As Range
   Dim xCell As Range
   Dim I As Long
   Dim J As Long
   Dim K As Long
   I = Worksheets("Sheet1").UsedRange.Rows.Count
   J = Worksheets("Sheet2").UsedRange.Rows.Count
   If J = 1 Then
      If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 0 Then J = 0
   End If
   Set xRg = Worksheets("Sheet1").Range("C:C" & I)
   On Error Resume Next
   Application.ScreenUpdating = False
   For K = 1 To xRg.Count
      If CStr(xRg(K).Value) = "Done" Then
         xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & J + 1)
         xRg(K).EntireRow.Delete
         If CStr(xRg(K).Value) = "Done" Then
             K = K - 1
         End If
         J = J + 1
      End If
   Next
   Application.ScreenUpdating = True
End Sub

In the code, Sheet1 is the worksheet that contains the row you want to move. And Sheet2 is the destination worksheet where you will locate the row. "C:C" is the column that contains the certain value, and the word "Done" is the certain value you will move the row based on. Please change them based on your needs.

Step 3

Then click F5 to complete the task.

This is how you can move an entire row to another sheet based on a cell value in Excel.

Note

If you want to copy data-based cell values, use the below code.

Code

Sub MoveRowBasedOnCellValue()
   Dim xRg As Range
   Dim xCell As Range
   Dim I As Long
   Dim J As Long
   Dim K As Long
   I = Worksheets("Sheet1").UsedRange.Rows.Count
   J = Worksheets("Sheet2").UsedRange.Rows.Count
   If J = 1 Then
   If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 0 Then J = 0
   End If
   Set xRg = Worksheets("Sheet1").Range("C1:C" & I)
   On Error Resume Next
   Application.ScreenUpdating = False
   For K = 1 To xRg.Count
      If CStr(xRg(K).Value) = "Done" Then
         xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & J + 1)
         J = J + 1
      End If
   Next
   Application.ScreenUpdating = True
End Sub

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can move an entire row to another sheet based on a cell value in Excel to highlight a particular set of data.