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
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
Good morning,
I'm very new to VBA, and this solution helped, but I need alittle but more. I have tabs with the months and need to pull the data via date range. For an example, if the purchase was made on 1/15/25, it would move the data to the jan tab. I have column "K" with the date.
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! 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?
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!
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 - 1from the macro provided by LeonPavesic
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 SubTry 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 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/EfdUyaFRvZtFjoBv20hNfvcBQIvyhXOsMU-2lU4zkPmiKA?e=LalAiH
What code do I use to copy rows from form to individual staff tabs, by the cell with email address name being used?
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- Wonder if you can help me as I'm at my wits end. I need to move rows from one sheet (ACC) to one of 16 other sheets based on the text listed in column A in sheet ACC. I've tried your code, but I must be doing something wrong. Please help. I'll try to attach for you.
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.
