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.
- 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: 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.
Hi,
I really need a code to move a line from one sheet to another, this code above only deletes the row and its not pulling through to the other sheet?
Can someone help?
Hello ASHLEIGH,
Thank you for your question. We’re sorry to hear that you’re facing difficulties with the VBA code. In fact, the ExcelDemy team has tested the Excel file and the code with other workbooks following your comment and the code appears to be working correctly.
However, you can check the following 4 steps.
1. You can choose any of the two formats according to your need from Method-2. Additionally, read the Notes given after each code. This will help you to understand the code properly.
2. You must create a worksheet where you want to move the filtered rows.
3. Set the Input Worksheet and Destination Worksheet names properly with the Range.
4. In this code, we have selected Cable as filter criteria. So, the rows containing Cable within the given Range will be deleted and it will be moved to your Destination Workbook. So, make sure you have provided the criteria according to your dataset.
I hope this will solve your issue. If you still face problems, please feel free to comment again or send your workbook through e-mail so that I can check the issue.
Hi, if I want to use different values and different sheets, can I use one module for this? For example: if a cell contains value A, then the row moves from sheet 1 to sheet 2, if it contains value B, then the row moves from sheet 1 to sheet 3. How do I implement this into the code?
Hello CAR
Thank you for reaching out to us on our website. You can use a single module to move rows between sheets based on various values. You may do this by using an IF statement inside a FOR loop that iterates through the rows in Sheet1, verifies the value, and then moves the entire row to the proper destination sheet based on the value. The intended code is given below.
Regards
Lutfor Rahman Shimanto
Hi again,
I finally had time to update the code in my spreadsheet, but it doesn’t seem to work. I probably made a mistake somewhere, do you think you could check this code to see if i did something wrong?
The value that needs to be selected is in column I, from row 3.
There are multiple values in a dropdown menu in this column, of which 4 values are used in the code:
If value “1. Cancelled”, then move to sheet “Cancelled Rejected Proposals”
If value “2. Rejected”, then move to sheet “Cancelled Rejected Proposals”
If value “9. Projectnumber assigned”, then move to sheet “Projects”
If value “10. Finished”, then move to sheet “Finished Projects”
The move from one sheet to another should also be as of row 3, since the first two rows are for titles.
I hope you can help, thank you so much in advance!
Sub MoveRowsInSheetsBasedOnValues()
Dim wsSource As Worksheet, wsDest As Worksheet
Dim i As Long, lastRow As Long, destRow As Long
Set wsSource = ThisWorkbook.Worksheets(“Acquisition”)
lastRow = wsSource.Cells(wsSource.Rows.Count, “I”).End(xlUp).Row
Set wsDest = ThisWorkbook.Worksheets(“Projects”)
destRow = 1
For i = 1 To lastRow
If wsSource.Cells(i, “I”).Value = “9. Projectnumber assigned” Then
wsSource.Rows(i).Copy wsDest.Rows(destRow)
destRow = wsDest.Cells(wsDest.Rows.Count, “I”).End(xlUp).Row + 1
End If
Next i
Set wsDest = ThisWorkbook.Worksheets(“Finished Projects”)
destRow = 1
For i = 1 To lastRow
If wsSource.Cells(i, “I”).Value = “10. Finished” Then
wsSource.Rows(i).Copy wsDest.Rows(destRow)
destRow = wsDest.Cells(wsDest.Rows.Count, “I”).End(xlUp).Row + 1
End If
Next i
Set wsDest = ThisWorkbook.Worksheets(“Cancelled Rejected Proposals”)
destRow = 1
For i = 1 To lastRow
If wsSource.Cells(i, “I”).Value = “1. Cancelled” Then
wsSource.Rows(i).Copy wsDest.Rows(destRow)
destRow = wsDest.Cells(wsDest.Rows.Count, “I”).End(xlUp).Row + 1
End If
Next i
Set wsDest = ThisWorkbook.Worksheets(“Cancelled Rejected Proposals”)
destRow = 1
For i = 1 To lastRow
If wsSource.Cells(i, “I”).Value = “2. Rejected” Then
wsSource.Rows(i).Copy wsDest.Rows(destRow)
destRow = wsDest.Cells(wsDest.Rows.Count, “I”).End(xlUp).Row + 1
End If
Next i
End Sub
Hello CAR,
It is great to see you again. I hope this reply finds you well. As you requested, I have reviewed your code and found some Syntax errors. But overall, the algorithm was close to achieving your goal.
However, I am introducing a more efficient way of doing the same task with a better algorithm that should be compatible with large datasets.
EXCEL VBA CODE:
I hope this will achieve your goal. I am also giving you the Solution workbook to help you understand better.
DOWNLOAD WORKBOOK
Regards
Lutfor Rahman Shimanto
Hi again,
You can disregard my question about deleting a row after moving it. I have figured it out myself:
wsSource.Rows(i).EntireRow.Delete
I’m so happy, thanks again!
Dear Car,
You are most welcome.
Regards
ExcelDemy
Wow, thank you so much! It works like a charm!
I even adjusted the code for another sheet within the workbook, with different values, and it works perfectly!
There is only one thing left, and I hope I am not bothering you too much with my questions.
How do I get these lines to be deleted after they moved to the next sheet? I looked it up online, and found this command, but I feel it’s not complete: EntireRow.Delete
As you may have noticed, I am new to this VBA, so I really appreciate your help!
Thank you for your help!
Thank you once again for your trust in us, CAR. Please do not hesitate to reach out if you need further assistance.
Regards
Team ExcelDemy
How would i use the code to have different values in the cell for example differen 10 digit numbers all starting with 430
Greetings Beck,
Thanks a lot for your question. I am not entirely sure if your question is pertinent to topic of the this article, or if it is just a standalone question. I am giving you a response treating the question as a standalone question.
To have different values in the cell which is 10 digits long and starts with 430, paste the below code in the code editor, and then press Run.
After pressing Run, you will notice that the code now put 10 distinct 10-digit values in the worksheet starting with 430.
Hope this helps, if you have any other question or suggestions,please do not hesitate to comment on this post.