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.

5 Replies


You can try Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table in worksheet "Tire cases" and right-click with the mouse and select refresh to update the green result table.


If you work with Office 365 or Excel 2021 you can apply the FILTER function.

best response confirmed by BlueMoose (Brass Contributor)

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
' 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.

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!