Excel-Move Rows to other rows based on Cell Value

[Robertson1995]

Hello. I am needing a code that will move specific rows to other rows in the same sheet based on a Cell Value. I have a sheet named "Inventory" that has values in Columns A-H. I need code that when I enter "S" in column I, it will copy the contents of A-H for that row and paste a copy of it in the first empty row in columns K-R and repeat everytime I enter an "S" in Column I, it will continue to move the A-H contents to the next empty row in columns K-R. I also need a separate code that once all of the data is moved, it will delete all rows in columns A-H that have an "S" in column I and shift the cells up. I hope this makes sense and thanks in advance for any help with this.

 

[Fluff]

How about this

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column <> 9 Or Target.Cells.Count <> 1 Then Exit Sub
    
        If UCase(Target.Value) = "S" Then
            With Range("A" & Target.Row).Resize(, 8)
                .Copy Range("K" & Rows.Count).End(xlUp).Offset(1)
                .Resize(, 9).Delete shift:=xlUp
            End With
        End If

End Sub

This needs to go into the sheet module, rather than a standard module.
Right click on the Inventory sheet tab>viewcode > paste the above into the code window that appears.

This will also delete the S in col I

 

[Robertson1995]

Thank you so much. That worked even better than I had hoped. I do have one question. I notice that when I enter the S and it deletes the row, I dont have an option to select the "Back" button. Do you know why? I would like to have that just in case I accidentely enter the S in the wrong row. Thanks.

 

[Fluff]

Unfortunately, when a macro runs it removes the ability to undo.

 

[Fluff]

This will give you the opportunity to reverse the process.
If you type s into col S the row will be moved back to the original data, but at the bottom.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count <> 1 Then Exit Sub

    Select Case Target.Column
    Case 9
        If UCase(Target.Value) = "S" Then
            With Range("A" & Target.Row).Resize(, 8)
                .Copy Range("K" & Rows.Count).End(xlUp).Offset(1)
                .Resize(, 9).Delete shift:=xlUp
            End With
        End If

    Case 19
        If UCase(Target.Value) = "S" Then
            With Range("K" & Target.Row).Resize(, 8)
                .Copy Range("A" & Rows.Count).End(xlUp).Offset(1)
                .Resize(, 9).Delete shift:=xlUp
            End With
        End If
    End Select

End Sub

 

[Robertson1995]

Worked great. That's even better than the back button. Thanks so much for your help with this.

 

[Fluff]

Glad to help & thanks for the feedback

 

[bronkt]

Hello I have a similar question, i need to move all rows that a cell value equals "closed" to another sheet and then return to 1st sheet and remove blank rows. i would like all the "closed" rows to move to 1 sheet (don't make a new sheet after running code) help please.