VBA to move a row to another sheet based on criteria

[survivalbloke]

Hello. First off, I apologize for having to ask this because I'm certain that the solution has already been posted elsewhere in this community, but I'm actually so inept in Excel, I'm not entirely sure I would recognize the solution if I saw it, so I'm asking. If based on my question, you know where a thread is which has solved this, please let me know!

I would like a way to automatically move an entire row to another sheet based on the contents of a specific cell in a row. Here's my(extremely simplified) example:


[TABLE="class: grid, width: 350, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ITEM[/TD]
[TD]STATUS[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]item 1[/TD]
[TD]for sale[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]item 2[/TD]
[TD]sold[/TD]
[/TR]
</tbody>[/TABLE]

In the above example, we will call that sheet "Inventory", and the second sheet(not shown) will be called "Sold". What I'd like is a way to automatically move any column which has a "status" of "sold". In the above example, row 2 should be moved to the sheet named "Sold". I'm hoping that is clear, but if for some reason it isn't, I'll explain what I'm doing. I've got a spreadsheet(which was made awesome thanks to the folks here) I use to track inventory. When I get inventory, I enter it in the "Inventory" sheet of the workbook. Once something is sold and no longer available, I cut and paste that row from the "Inventory" sheet to the "Sold" sheet.

Also, if this solution is based on VBA, how does one execute a VBA script?

Thanks!

 

[tropiqz]

Assuming you want to us Sold or Cancelled

And always copy to sheet named Sold
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/28/2018  10:48:41 AM  EDT
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets("Sold").Cells(Rows.Count, "B").End(xlUp).Row + 1
If Target.Value = "Sold" Or Target.Value = "Cancelled" Then
Rows(Target.Row).Copy Destination:=Sheets("Sold").Rows(Lastrow)
Rows(Target.Row).Delete
End If
End If
End Sub

Or do you mean if Sold copy to sheet named Sold and if Cancelled copy to sheet names Cancelled

BOTH Sold and Cancelled going to the same sheet, so the top one should work. Thanks for the help!

 

[My Aswer Is This]

So I'm assuming you mean my last post worked for you:
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[Lewiskj]

I used your code above and it worked great.
If I could ask one question?
I have my data in a table on the target sheet, but it copies it to the row below the table.
I then have to go to the target sheet and drag the table down over the new row.

Is there a way to make it copy the data into the table?

 

[My Aswer Is This]

The term "Table" is used very loosely on this forum by a lot of Users. A Excel "Table" is a Range on a Excel Sheet.

Any time you use the term "Table" you need to provide the Table name.

Like "Table1" or any other name you may give your Table

Or if your referring to a sheet you need to provide the sheet name.

 

[Lewiskj]

OK thanks for the heads up.
I am trying to get the row moved to a sheet called "Members paid (2019)" and into a table on that sheet called "Table1"

 

[MTROWBRIDGE]

Survival.

I discovered one mistake in my script.
Try this script instead:

Private Sub Worksheet_Change(ByVal Target As Range)
'Version 2
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets("Sold").Cells(Rows.Count, "B").End(xlUp).Row + 1

If Target.Value = "Sold" Then
Rows(Target.Row).Copy Destination:=Sheets("Sold").Rows(Lastrow)
Rows(Target.Row).Delete
End If
End If
End Sub

have tried this code and it is not working for some reason? Weird part is if I change the name of the second sheet from sold I get a run time error '9': subscript out of range. But if I change the page back to Sold it does nothing.
I

 

[rvinci]

Survival.

I discovered one mistake in my script.
Try this script instead:

Private Sub Worksheet_Change(ByVal Target As Range)
'Version 2
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets("Sold").Cells(Rows.Count, "B").End(xlUp).Row + 1

If Target.Value = "Sold" Then
Rows(Target.Row).Copy Destination:=Sheets("Sold").Rows(Lastrow)
Rows(Target.Row).Delete
End If
End If
End Sub

Is there a way to automate this even further?

Right now, our employees would enter a date say in column A (using the existing example code). Column B then uses an If(isblank) formula to populate whether either Open or Resolved. Upon them entering a date in A, B will say Resolved. Once they enter the date and column B populates to Resolved, I want it to move to Sheet2 without the employee having to actually go into the cell in Column B and hit enter.

Hopefully that all makes sense.

 

[My Aswer Is This]

Is there a way to automate this even further?

Right now, our employees would enter a date say in column A (using the existing example code). Column B then uses an If(isblank) formula to populate whether either Open or Resolved. Upon them entering a date in A, B will say Resolved. Once they enter the date and column B populates to Resolved, I want it to move to Sheet2 without the employee having to actually go into the cell in Column B and hit enter.

Hopefully that all makes sense.

I suggest you start a new posting and be very specific what your attempting to do.
This post is more then 5 years old.