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

Get FREE Advanced Excel Exercises with Solutions!

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 Shine

Aung Shine

My name is Aung. I have my B.Sc. degree in EEE. From now on, I will be working with 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.

13 Comments
  1. 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.

  2. 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?

    • Reply Avatar photo
      Lutfor Rahman Shimanto Apr 13, 2023 at 11:18 AM

      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.

      
      Sub MoveRowsInSheetsBasedOnValues()
      
          Dim wsSource As Worksheet, wsDest As Worksheet
          Dim i As Long, lastRow As Long, destRow As Long
          
          Set wsSource = ThisWorkbook.Worksheets("Sheet1")
          lastRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row
          
          Set wsDest = ThisWorkbook.Worksheets("Sheet2")
          destRow = 1
          
          For i = 1 To lastRow
              If wsSource.Cells(i, "B").Value = "A" Then
                  wsSource.Rows(i).Copy wsDest.Rows(destRow)
                  destRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Row + 1
              End If
          Next i
          
          Set wsDest = ThisWorkbook.Worksheets("Sheet3")
          destRow = 1
          
          For i = 1 To lastRow
              If wsSource.Cells(i, "B").Value = "B" Then
                  wsSource.Rows(i).Copy wsDest.Rows(destRow)
                  destRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Row + 1
              End If
          Next i
          
      End Sub
      

      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

        • Avatar photo
          Lutfor Rahman Shimanto May 14, 2023 at 2:48 PM

          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:

          
          
          Sub MoveRowsInSheetsBasedOnValuesNew()
              
              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
              
              
              For i = 3 To lastRow
              
                  If wsSource.Cells(i, "I").Value = "9.Projectnumber assigned" Then
                      
                      Set wsDest = ThisWorkbook.Worksheets("Projects")
                      destRow = wsDest.Cells(wsDest.Rows.Count, "I").End(xlUp).Row + 1
                      
                      wsSource.Rows(i).Copy wsDest.Rows(destRow)
                  
                  ElseIf wsSource.Cells(i, "I").Value = "10.Finished" Then
                      
                      Set wsDest = ThisWorkbook.Worksheets("Finished Projects")
                      destRow = wsDest.Cells(wsDest.Rows.Count, "I").End(xlUp).Row + 1
                      
                      wsSource.Rows(i).Copy wsDest.Rows(destRow)
                  
                  ElseIf wsSource.Cells(i, "I").Value = "1.Cancelled" Then
                      
                      Set wsDest = ThisWorkbook.Worksheets("Cancelled Rejected Proposals")
                      destRow = wsDest.Cells(wsDest.Rows.Count, "I").End(xlUp).Row + 1
                      
                      wsSource.Rows(i).Copy wsDest.Rows(destRow)
                  
                  ElseIf wsSource.Cells(i, "I").Value = "2.Rejected" Then
                      
                      Set wsDest = ThisWorkbook.Worksheets("Cancelled Rejected Proposals")
                      destRow = wsDest.Cells(wsDest.Rows.Count, "I").End(xlUp).Row + 1
                      
                      wsSource.Rows(i).Copy wsDest.Rows(destRow)
                  
                  End If
              
              Next i
              
          End Sub
          
          

          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!

        • Avatar photo
          Shamima Sultana May 14, 2023 at 11:03 AM

          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!

        • Avatar photo
          Lutfor Rahman Shimanto May 11, 2023 at 10:27 AM

          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

  3. How would i use the code to have different values in the cell for example differen 10 digit numbers all starting with 430

    • Reply Avatar photo
      Rubayed Razib Suprov May 7, 2023 at 11:34 AM

      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.

      
      Sub generate_numbers()
          Dim i As Long
          Dim myrange As Range
          Set myrange = Range("B3:B12")
          For i = 1 To myrange.Cells.Count
              myrange.Cells(i) = "430" & Format(i, "0000000000")
          Next i
      End Sub
      

      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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo