Forum Discussion
Solved
How to move entire row to another sheet based on cell value in Excel
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.
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
BlueMoose you could use the FILTER function to pull out the rows where Col X equals the word TIRES, just as OliverScheurich had suggested.
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
Hi LeonPavesic,
Can you advise how adjust this code if I need to copy rows from multiple worksheets?
I have worksheets by week named “WK01”, “WK02”… and need to copy rows with same criteria to sheet “Summary”.
ThanksLeonPavesic I am trying to move certain cells from one sheet (Inventory List)to another (Order) based on the value in Column L. I have a set value in Column K for when to re-order stock, and if any number greater than 0 appears in Column L, then the certain columns will be moved to the "Order" sheet. I have tried the code in the discussion, but I cannot figure out how to do the code for a figure and not a text.https://1drv.ms/x/s!Ap8C9VxVJYr2goYfM5Ru7YU-72b_Gw?e=fN3IEl
- I have copied this code, changing the sheet names and the column location for the criteria I want moved to another sheet. I run the macro and nothing happens.
My sheets are "Monitoring Invoices" (source) and "NOT ASSIGNED" (target). The criteria "Not Assigned" is in column C.
The Monitoring Invoices sheet grows each month, so I need the Not Assigned rows to move to the NOT ASSIGNED sheet and accumulate for review.
Can you assist me by editing what you have provided to fit my needs? I'm new to VBA and can't figure out what I'm doing wrong - THANKS!
