Cookies help us deliver our services. By using our services, you agree to our use of cookies.
Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to move entire row to another sheet based on cell value in Excel?

For moving entire row to another sheet based on cell value, this article will help you.

Move entire row to another sheet based on cell value with VBA code

Move entire row to another sheet based on cell value with Kutools for Excel


Easily select entire rows based on cell value in a certian column:

The Select Specific Cells utility of Kutools for Excel can help you quickly select entire rows based on cell value in a certian column in Excel as below screenshot shown. After selecting all rows based on cell value, you can manually move or copy them to a new location as you need in Excel.

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download the free trial Now!


Move entire row to another sheet based on cell value with VBA code


As below screenshot shown, you need to move the entire row from Sheet1 to Sheet2 if a specific word “Done” exists in column C. You can try the following VBA code.

1. Press Alt+ F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the below VBA code into the window.

VBA code 1: Move entire row to another sheet based on cell value

Sub Cheezy()
'Updated by Kutools for Excel 2017/8/28
    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)
            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

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

3. Press the F5 key to run the code, then the row which meet the criteria in Sheet1 will be moved to Sheet2 immediately.

Note: The above VBA code will delete rows from the original data after moving to a specified worksheet. If you only want to copy rows based on cell value instead of deleting them. Please apply the below VBA code 2.

VBA code 2: Copy entire row to another sheet based on cell value

Sub MoveRowBasedOnCellValue()
'Updated by Extendoffice 2017/11/10
    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

Move entire row to another sheet based on cell value with Kutools for Excel

If you are newbie in VBA code. Here I introduce the Select Specific Cells utility of Kutools for Excel. With this utility, you can easily select all rows based on a certain cell value or different cell values in a worksheet, and the copy the selected rows to the destination worksheet as you need. Please do as follows.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. 

1. Select the column list contains the cell value you will move rows based on, then click Kutools > Select > Select Specific Cells. See screenshot:

2. In the opening Select Specific Cells dialog box, choose Entire row in the Selection type section, select Equals in the Specific type drop-down list, enter the cell value into the text box and then click the OK button.

Another Select Specific Cells dialog box pops up to show you number of selected rows, and meanwhile, all rows contain the specified value in selected column have been selected. See screenshot:

3. Press the Ctrl + C keys to copy the selected rows, and then paste them into the destination worksheet you need.

Note: If you want to move rows to another worksheet based on two different cell values. For example, move rows based on cell values either "Done" or "Processing", you can enable the Or condition in the Select Specific Cells dialog box as below screenshot shown:

Tip.If you want to have a free trial of this utility, please go to download the software freely first, and then go to apply the operation according above steps.


Related articles:



Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Anne · 2 days ago
    Hello, thank you so much for this post. Instead of only "Done" I have several words to find, it can be around 100. I have them all in Column A of Sheet 2. I need to find those words from Sheet 1 and paste the entire row(s) in sheet 3, if the words match. How can I do that? I would really appreciate your help.
  • To post as a guest, your comment is unpublished.
    Anju · 3 days ago
    Hi, I have a sheet where are liscence renewal details are present, when the due date is nearing (before 2 months) those liscence details need to sent as an email to a single recipient. I have used today formula and calculated the days remaining from the due date. So I am using that cell- if the value is above 60, it must copy the entire cell and put it into another workbook. It has to repeat this until it reaches the end. could you help me writing a code on this ?
  • To post as a guest, your comment is unpublished.
    Anne · 3 days ago
    Hello, thank you so much for this code. How To Move Entire Row To Another Sheet Based On a column? Let's say in sheet 2, I have Case IDs in column A. And I need to find anything associated with those Case IDs in Sheet 1 and paste it in Sheet 3. Can you please help me do that?
  • To post as a guest, your comment is unpublished.
    TJ · 6 days ago
    Thanks, this helped me alot. I am not an Excel expert! I used the the module in VBA you created to transfer rows from Sheet 1 to Sheet 2. My project is that I'm moving objects to designated locations that were set up in a certain order in another column located in Sheet 1. When I run the module, I lose the location because the rows shift up in Sheet 1 after the transfer. I have to insert a row and type in the designated location again. Can it be set up so that I can at least keep the blank row and just type in the location needed?
  • To post as a guest, your comment is unpublished.
    SB · 1 months ago
    Thank you! If it is not too much trouble could you please post how to have the destination data overwrite vs. append to the last line? Specifically to overwrite data starting at A2. Thank you!