SOLVED

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

Brass Contributor

Okay, so I think this is not something that can be done through formulas in Excel.  I know this kind of thing can be done through formulas in Google Sheets; but, I think this is going to require macros or VBA (an area in which I have very little experience and slightly scares me).  If there IS a formula for this, that's my preference.

 

Basically, I want to pull entire rows from one sheet to another based on what is in one cell of the row.  I've attached an example.  Basically, every time the word "TIRES" appears in column X of the Cases tab, I want that entire row to show in the Tire Cases tab.  Not that it's super important the why of it, but I'm trying to separate out data sections for overall statistics.

 

Also, this is for a document that will be regularly added to and so will need to continue adding as new rows with "TIRES" in column X appear.

33 Replies
best response confirmed by BlueMoose (Brass Contributor)
Solution

Hi @BlueMoose,

In Excel, as far as I know, it is not possible to achieve the desired functionality using formulas alone. This task typically requires the use of macros or VBA (Visual Basic for Applications) coding.

To accomplish your goal of pulling entire rows from one sheet to another based on the presence of the word "TIRES" in column X, you will need to use VBA. Here is an example of a VBA macro that could help you achieve this:

1. Press `Alt + F11` to open the VBA editor in Excel.

2. Insert a new module by clicking "Insert" > "Module".

3. Paste the following VBA code into the module:


Sub MoveRowsToTireCases()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim i As Long

' Set the source and target sheets
Set sourceSheet = ThisWorkbook.Worksheets("Cases")
Set targetSheet = ThisWorkbook.Worksheets("Tire Cases")

' Find the last row in the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "X").End(xlUp).Row

' Loop through each row in the source sheet
For i = 2 To lastRow
' Check if cell in column X contains "TIRES"
If sourceSheet.Cells(i, "X").Value = "TIRES" Then
' Copy the entire row to the target sheet
sourceSheet.Rows(i).Copy Destination:=targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1)
' Delete the row from the source sheet
sourceSheet.Rows(i).Delete
' Decrement the loop counter as the rows are shifting up
i = i - 1
' Update the last row value
lastRow = lastRow - 1
End If
Next i
End Sub

4. Modify the macro if your sheet names differ from "Cases" and "Tire Cases". Ensure that you keep the worksheet names within double quotes.

5. Close the VBA editor and return to your Excel workbook.

6. Press `Alt + F8` to open the "Macro" dialog box.

7. Select the "MoveRowsToTireCases" macro and click "Run" 

By running this VBA macro, it should scan the "Cases" sheet, identify rows with "TIRES" in column X, and move those entire rows to the "Tire Cases" sheet. The rows shloud be copied to the bottom of the "Tire Cases" sheet and deleted from the "Cases" sheet. The macro will continue to work even as new rows with "TIRES" in column X are added to the "Cases" sheet.

Ensure that you save your workbook in a macro-enabled format (.xlsm) to retain the macro functionality.


I know that is not really a solution that you were lookin (hoping) for, but perhaps you can try it.



Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.


If the post was useful in other ways, please consider giving it Like.


Kindest regards,


Leon Pavesic

@BlueMoose you could use the FILTER function to pull out the rows where Col X equals the word TIRES, just as @OliverScheurich had suggested.

Thanks! This allowed me to do that and then work through the data on a new sheet.

@LeonPavesic 
I was able to edit the VBA you provided here to work for my sheets/cell, but I'm wanting to take it one step further. 

Is it possible to have a single module check the same cell for multiple criteria and move the rows to multiple sheets? 

For instance, I want rows with the value of "202221" in column P on the "Weekly" sheet deleted and moved to the "202221" sheet and values of "202321" in column P on the "Weekly" sheet deleted and moved to the "202321" sheet. 

Would this be possible to accomplish with a single module? Or would I need multiple?

Thanks in advance! 

@LeonPavesic Hi, if I do not want to move the entire row but a range (say from B to J), what will the code be.

@LeonPavesic How can you add onto your script if the value changes on your sourcesheet to delete it off your target sheet

 

ex. tires changes to wheels on source sheet

so now you want to delete the row from the "tires" worksheet 

@LeonPavesic when I used this macro, it did not add the moved rows underneath the last row of the target sheet.  Instead, it sequentially replaced the same row, so only the last moved row is showing on the target sheet.  I know this must be the VBA line that needs amending, but not sure what needs to change:

sourceSheet.Rows(i).Copy Destination:=targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1)

@LeonPavesic Could you assist me with a code for my sheet? I want to be able to type "complete" in column N in the 5 tabbed sheets that I have and they automatically move from that sheet to the "complete" sheet.Code Help.png

@LeonPavesic 

What code do I use to copy rows from form to individual staff tabs, by the cell with email address name being used?

@dcaton 

Try this macro:

Sub SplitData()
    Dim wshS As Worksheet
    Dim wshT As Worksheet
    Dim r As Long
    Dim r0 As Long
    Dim m As Long
    Dim ID
    Application.ScreenUpdating = False
    Set wshS = ActiveSheet
    m = wshS.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    wshS.Range("1:" & m).Sort Key1:=wshS.Range("E1"), Header:=xlYes
    r = 2
    Do
        If wshS.Range("E" & r).Value <> wshS.Range("E" & r - 1).Value Then
            Set wshT = Worksheets.Add(After:=Worksheets(Worksheets.Count))
            wshT.Name = wshS.Range("E" & r).Value
            wshT.Range("1:1").Value = wshS.Range("1:1").Value
            ID = wshS.Range("E" & r).Value
            r0 = r
            Do While wshS.Range("E" & r + 1).Value = ID
                r = r + 1
            Loop
            wshT.Range("2:" & r - r0 + 2).Value = wshS.Range(r0 & ":" & r).Value
        End If
        r = r + 1
    Loop Until r > m
    Application.ScreenUpdating = True
End Sub

@LeonPavesic I've been trying to use the VBA code you previously gave out for the Tire example, but I can't get it to work within my sheets. I've changed the name and which column/value to look for but I can't seem to get it to run properly. I've included a brief example of the sheet - I would like for the rows to be moved when "Sold" is typed in column H and have it be moved to the "24Sales" tab if possible. 

 

Thank you in advance!

 

https://iowastate-my.sharepoint.com/:x:/g/personal/ambishop_iastate_edu/EfdUyaFRvZtFjoBv20hNfvcBQIvy... 

@LeonPavesic Hi, I copied the code you advised the original poster so I could use it for the same effect and it worked perfectly when i changed the sheet names etc so thanks for that.

 

The only thing I found is that when I refresh and the rows copy across to the target sheet, it doesnt add to the next row in the target sheet, it seems to simply overwrite the previous row that was copied.

 

Is this something to do with the loop counter? 

@LeonPavesic  - As I mentioned, even if i update 10 rows of data with the right criteria to be moved to the target sheet (Lost), only one row is moving and the rest disappear completely. If there is already a row in the target sheet, it gets overwritten. Any advice would be amazing.....I am literally one week into trying to teach myself macros so im not the best at this but I need to find a way around it for a work report im doing!

 

Below is the code I have used for this but I cannot work out what has gone wrong.

 

Sub MoveToLost()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim i As Long

' Set the source and target sheets
Set sourceSheet = ThisWorkbook.Worksheets("Master")
Set targetSheet = ThisWorkbook.Worksheets("Lost")

' Find the last row in the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "M").End(xlUp).Row

' Loop through each row in the source sheet
For i = 1 To lastRow
' Check if cell in column M contains "Lost"
If sourceSheet.Cells(i, "M").Value = "Lost" Then
' Copy the entire row to the target sheet
sourceSheet.Rows(i).Copy Destination:=targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1)
' Delete the row from the source sheet
sourceSheet.Rows(i).Delete
' Decrement the loop counter as the rows are shifting up
i = i - 1
' Update the last row value
lastRow = lastRow - 1
End If
Next i
End Sub

@DazNightvision 

Try this. Please test on a copy of your workbook first.

Sub MoveToLost()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim targetRow As Long

    ' Set the source and target sheets
    Set sourceSheet = ThisWorkbook.Worksheets("Master")
    Set targetSheet = ThisWorkbook.Worksheets("Lost")

    ' Find the last row in the source sheet
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "M").End(xlUp).Row

    ' Last row in column M on the target sheet
    targetRow = targetSheet.Cells(targetSheet.Rows.Count, "M").End(xlUp).Row

    ' Loop through each row in the source sheet
    For i = lastRow To 1 Step -1
        ' Check if cell in column M contains "Lost"
        If sourceSheet.Cells(i, "M").Value = "Lost" Then
            ' Increment target row
            targetRow = targetRow + 1
            ' Copy the entire row to the target sheet
            sourceSheet.Rows(i).Copy Destination:=targetSheet.Cells(targetRow, 1)
            ' Delete the row from the source sheet
            sourceSheet.Rows(i).Delete
        End If
    Next i
End Sub

@LeonPavesic 

 

Can this be amended so the data on the original sheet is not deleted.

 

I tried to amend by am not getting Compile error: Only comments may appear after End Sub, End Function or End Property and I'm lost!

 

@chwar750 

Delete the lines

' Delete the row from the source sheet
sourceSheet.Rows(i).Delete
' Decrement the loop counter as the rows are shifting up
i = i - 1
' Update the last row value
lastRow = lastRow - 1

from the macro provided by @LeonPavesic 

@LeonPavesic I hope I’m not too late to the party to ask questions, but I tried to use your codes, and I ran into some issues. My table starts on column B, row 4 so I offset 2 instead of 1, but every time I run the macro, it replaces the old information (on the now sheet) with new information, so instead of a running list of items that contain “‘Tires’” I continue to get one row with the new information replacing the old stuff. How to I make it accumulate?

@Hans Vogelaar 

Hi Hans,

 

Thanks for all the great responses I am getting close using the code I have pasted below!

 

I am trying to return unique numbers from column B of the "Ideate" tab to column B of the "Validate & Plan" tab on the next empty row of the "Validate & Plan" tab based off column X in the "Ideate" tab being "Yes".

 

The two problems I am running into are:

1) If for example projects 1 and 10 in column B of the "Ideate" tab are flagged as "Yes" in column X it will return 1 in the next empty row in column B of the "Validate & Plan" tab (i.e. B3), however, it will not return 10 below that, it will enter a formula i.e. "=B3+1" giving a value of 2. If project 25 was "Yes" as well, it would return a 3 below the 2 using the formula "=B4+1" giving a value of 3.

 

2) If I re-run the macro it will double count projects that have already been pulled across to the "Validate & Plan" tab i.e. project 1 will appear again in column B of the "Validate & Plan" tab. I only want new projects that have been tagged as "Yes" on the "Ideate" tab to be brought across in the next empty row. FYI all the project IDs are numerical and unique.

 

I hope this makes sense. Any help would be greatly appreciated.

---

 

Sub MoveRowsToTireCases()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim i As Long

' Set the source and target sheets
Set sourceSheet = ThisWorkbook.Worksheets("Ideate")
Set targetSheet = ThisWorkbook.Worksheets("Validate & Plan")

' Find the last row in the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "X").End(xlUp).Row

' Loop through each row in the source sheet
For i = 6 To lastRow
' Check if cell in column X contains "Yes"
If sourceSheet.Cells(i, "X").Value = "Yes" Then
' Copy the entire row to the target sheet
sourceSheet.Cells(i, "B").Copy Destination:=targetSheet.Cells(targetSheet.Rows.Count, "B").End(xlUp).Offset(1)

End If
Next i
End Sub

@FPRaf 

Does this version do what you want?

Sub MoveRowsToTireCases()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim v As Variant

    ' Set the source and target sheets
    Set sourceSheet = ThisWorkbook.Worksheets("Ideate")
    Set targetSheet = ThisWorkbook.Worksheets("Validate & Plan")

    ' Find the last row in the source sheet
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "X").End(xlUp).Row

    ' Loop through each row in the source sheet
    For i = 6 To lastRow
        ' Check if cell in column X contains "Yes"
        If sourceSheet.Cells(i, "X").Value = "Yes" Then
            v = sourceSheet.Cells(i, "B").Value
            ' Check whether value has already been copied
            If targetSheet.Range("B:B").Find(What:=v, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
                ' Copy the value to the target sheet
                targetSheet.Cells(targetSheet.Rows.Count, "B").End(xlUp).Offset(1).Value = v
            End If
        End If
    Next i
End Sub
1 best response

Accepted Solutions
best response confirmed by BlueMoose (Brass Contributor)
Solution

Hi @BlueMoose,

In Excel, as far as I know, it is not possible to achieve the desired functionality using formulas alone. This task typically requires the use of macros or VBA (Visual Basic for Applications) coding.

To accomplish your goal of pulling entire rows from one sheet to another based on the presence of the word "TIRES" in column X, you will need to use VBA. Here is an example of a VBA macro that could help you achieve this:

1. Press `Alt + F11` to open the VBA editor in Excel.

2. Insert a new module by clicking "Insert" > "Module".

3. Paste the following VBA code into the module:


Sub MoveRowsToTireCases()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim i As Long

' Set the source and target sheets
Set sourceSheet = ThisWorkbook.Worksheets("Cases")
Set targetSheet = ThisWorkbook.Worksheets("Tire Cases")

' Find the last row in the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "X").End(xlUp).Row

' Loop through each row in the source sheet
For i = 2 To lastRow
' Check if cell in column X contains "TIRES"
If sourceSheet.Cells(i, "X").Value = "TIRES" Then
' Copy the entire row to the target sheet
sourceSheet.Rows(i).Copy Destination:=targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Offset(1)
' Delete the row from the source sheet
sourceSheet.Rows(i).Delete
' Decrement the loop counter as the rows are shifting up
i = i - 1
' Update the last row value
lastRow = lastRow - 1
End If
Next i
End Sub

4. Modify the macro if your sheet names differ from "Cases" and "Tire Cases". Ensure that you keep the worksheet names within double quotes.

5. Close the VBA editor and return to your Excel workbook.

6. Press `Alt + F8` to open the "Macro" dialog box.

7. Select the "MoveRowsToTireCases" macro and click "Run" 

By running this VBA macro, it should scan the "Cases" sheet, identify rows with "TIRES" in column X, and move those entire rows to the "Tire Cases" sheet. The rows shloud be copied to the bottom of the "Tire Cases" sheet and deleted from the "Cases" sheet. The macro will continue to work even as new rows with "TIRES" in column X are added to the "Cases" sheet.

Ensure that you save your workbook in a macro-enabled format (.xlsm) to retain the macro functionality.


I know that is not really a solution that you were lookin (hoping) for, but perhaps you can try it.



Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.


If the post was useful in other ways, please consider giving it Like.


Kindest regards,


Leon Pavesic

View solution in original post