Forum Discussion

BlueMoose's avatar
BlueMoose
Brass Contributor
Jul 14, 2023
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 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

  • LeonPavesic's avatar
    LeonPavesic
    Silver 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
    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

    • Yuriy_s2000's avatar
      Yuriy_s2000
      Copper Contributor

      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”. 
      Thanks

    • rickert91's avatar
      rickert91
      Copper Contributor

      LeonPavesic 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 

    • beancounter0529's avatar
      beancounter0529
      Copper Contributor
      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!

Resources