Forum Discussion
How to move entire row to another sheet based on cell value in Excel
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 Sub4. 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
Does this do what you want?
Sub MoveToCompleted()
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("Requests")
Set targetSheet = ThisWorkbook.Worksheets("Completed")
' Find the last row in the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
' Loop through each row in the source sheet
For i = lastRow To 1 Step -1
' Check if cell in column A contains "Complete"
If sourceSheet.Cells(i, "A").Value = "Complete" Then
' Copy the entire row
sourceSheet.Rows(i).Copy
' Insert in row 2 of the target sheet
targetSheet.Rows(2).Insert
' Delete the row from the source sheet
sourceSheet.Rows(i).Delete
End If
Next i
End Sub
HansVogelaar Thank you for providing so much information in this thread. I am working on something similar and have one issue I can't resolve. As new information is added to the source sheet I want run the macro and only the new information copy to the target sheet. As I currently have it written, the rows are duplicating on the target sheet each time I run the macro. The code I'm using is below.
Sub MoveRowsToSFU()
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("2024 Job List")
Set targetSheet = ThisWorkbook.Worksheets("SFU")
'Find the last row in the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "H").End(xlUp).Row
'Loop through each row in the source sheet
For i = 2 To lastRow
'Check if cell in column H contains "SFU"
If sourceSheet.Cells(i, "H").Value = "SFU" Then
'Copy the entire row
sourceSheet.Rows(i).Copy
'Insert in row 2 of the target sheet
targetSheet.Rows(2).Insert
End If
Next i
End Sub
- Thank you for the response. I'm currently using 365 at home but need this at work. I do believe the change is coming though so I'll check out the filter option as soon as possible. Thanks again - I appreciate the help.
That would only copy new rows. Changes in existing rows would not be reflected.
Do you have Microsoft 365? If so, you could use the FILTER function instead of VBA code, as suggested in one of the earliest replies in this discussion.
HansVogelaar No, there isn't. Would adding a "date added" column work and only run the macro at the end of my work day? But if I do that, will any new information added to existing columns transfer over or will it only appear on the source sheet?
HansVogelaar thank yo so much for responding. I was able to figure out the VBA. It wasnt as hard as i thought. Just very new to VBA. Thank you again for responding
Respectfully
Martin
Is there a column or combination of columns that uniquely identifies the rows, so that we can determine which rows are new?
