VBA - Moving row to another table

[wedloski]

Hey all!
I have a tough time doing a table in Excel that my dad asked me to do :P. I'm totally new into VBA and I could use some help from you. The thing is that I have 2 identical tables in separate sheets. My goal is to move a row from table in sheet1(in progress) to the table in sheet2(status). The information in whole row can be moved to the other table only if the condition will be met. In this scenario a project has to be accepted (in column W there are 3 options to choose - accepted, negotiation in progress, rejected). So whenever someone will choose that the project is accepted I would like the whole row to be moved into the same table but in another sheet on the bottom, right under other projects. I have tried using this:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Application.EnableEvents = False
    If Target.Column = 23 And Target.Value = "Accepted" Then
        LrowCompleted = Sheets("Status").Cells(Rows.Count, "A").End(xlUp).Row
        Range("A" & Target.Row & ":W" & Target.Row).Copy Sheets("Status").Range("A" & LrowCompleted + 1)
        Range("A" & Target.Row & ":W" & Target.Row).Delete xlShiftUp
    End If
    Application.EnableEvents = True
End Sub

But as far as it is deleting the row from sheet1 it is not showing up inside the table in sheet2. I have been trying to find a solution connected to naming a table, but I simply do not understand how to use it or if it's even the correct lead to follow.
Your help will be very much appreciated!
Please let me know if you need more explanation in order to get the understanding of what I mean by that

 

[DanteAmor]

Hi and welcome to MrExcel.

Try this. Change "Table2" by the name of your table in the Status sheet

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim tbl As ListObject
  Dim i As Long
  If Not Intersect(Target, Range("W:W")) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Value = "Accepted" Then
      Set tbl = Sheets("Status").ListObjects("Table2")   'table on Status sheet
      tbl.ListRows.Add , 1
      i = tbl.ListRows.Count
      tbl.DataBodyRange(i, 1).Resize(1, 23).Value = Range("A" & Target.Row).Resize(1, 23).Value
      Application.EnableEvents = False
      Target.EntireRow.Delete
      Application.EnableEvents = True
    End If
  End If
End Sub

 

[Yongle]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("W2").Resize(Rows.Count - 1)) Is Nothing Then
        If Target = "Accepted" Then
            With Target.EntireRow
                Application.EnableEvents = False
                .Copy Sheets("Status").Range("A" & Rows.Count).End(xlUp).Offset(1)
                .Delete
                Application.EnableEvents = True
            End With
        End If
    End If
End Sub

 

[wedloski]

Thank you both for finding time to help me with that! However I am getting a run-time error "9" alert in the line "Set tbl", I have tried to change the name to the name I can find (ctrl+G), but it doesn't help.

Hi and welcome to MrExcel.

Try this. Change "Table2" by the name of your table in the Status sheet

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim tbl As ListObject
  Dim i As Long
  If Not Intersect(Target, Range("W:W")) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Value = "Accepted" Then
      Set tbl = Sheets("Status").ListObjects("Table2")   'table on Status sheet
      tbl.ListRows.Add , 1
      i = tbl.ListRows.Count
      tbl.DataBodyRange(i, 1).Resize(1, 23).Value = Range("A" & Target.Row).Resize(1, 23).Value
      Application.EnableEvents = False
      Target.EntireRow.Delete
      Application.EnableEvents = True
    End If
  End If
End Sub

In this scenario I can see the movement, the only thing is that it's moving the row under the table on the same sheet. The other problem is that the cell W is a drop-down list and when "Accepted" is chosen, whole row is being moved together with my preset table for data validation.

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("W2").Resize(Rows.Count - 1)) Is Nothing Then
        If Target = "Accepted" Then
            With Target.EntireRow
                Application.EnableEvents = False
                .Copy Sheets("Status").Range("A" & Rows.Count).End(xlUp).Offset(1)
                .Delete
                Application.EnableEvents = True
            End With
        End If
    End If
End Sub

I have no clue where do I make the mistakes, would it be even possible to move to another table then?

 

[DanteAmor]

run-time error "9" alert in the line "Set tbl", I have tried to change the name to the name I can find (ctrl+G)

What do you mean by "Ctrl G", to know the name of your table, go to the "Status" sheet, select the table, in the Desing menu (far right) in the first option (left side) you can see the name from the table.
Change in the macro: "Table2" by the name of your table.

 

[wedloski]

What do you mean by "Ctrl G", to know the name of your table, go to the "Status" sheet, select the table, in the Desing menu (far right) in the first option (left side) you can see the name from the table.
Change in the macro: "Table2" by the name of your table.

Thank you for the hint!
You were right, after changing the name of the table to a specific name it is working indeed. but it's moving the row to the bottom of the table where the row is. So to be specific, the sheet where "Table2" is being used, is called Sheet2. What I am aiming for is to have those rows moved to Sheet1 where exactly the same table is. As far as I understand (not much to be honest), the problem is in the part where the macro is moving the row, the only difference would be to point it to another sheet instead? Correct me if I am wrong. Nevertheless I am not capable of doing that on my own. I would appreciate your help very much.

 

[DanteAmor]

My goal is to move a row from table in sheet1(in progress) to the table in sheet2(status).

That is your original request.

So now I don't understand what you need.

 

[wedloski]

That is your original request.

So now I don't understand what you need.

So maybe I will start from the scratch. Sheet1 is called "In Progress", where a table for managing projects is. What I have done so far is additional sheet (Sheet2 - Status) and Copy/Paste the table with additional column (W) for the status where the drop-down list appears in order to choose "accepted;negotiation;rejected). So my goal is to have the row in which cell W has the status "accepted" to be moved to the table in Sheet1 on the bottom right under projects that are in progress. I hope it makes sense.
Because right now when I want to test it, I am inserting data about the project to the table in Sheet2 and when I choose the "accepted" value in cell W, it's moving the row to the bottom of the table in Sheet2, so the exact same table in where I am typing in the data.

 

[DanteAmor]

It is clear to me, you want to copy from an origin and paste into a destination.
You could be more specific like this:

Name of the Origin Data:
- Sheet
- Table
- Column (accepted)
- Columns to copy

Name Data Destination:
- Sheet
- Table
- Row (I guess at the end of the table)
- Column (I suppose from the initial column of table)

 

[wedloski]

Sorry for the inconvenience, following the data needed template you have provided.

Name of the Origin Data:
- Sheet - "Project Status"
- Table - "Status"
- Column (accepted) - Column W
- Columns to copy - Columns from A to V, without the column of status

Name Data Destination:
- Sheet - "Projects in progress"
- Table - "Projects"
- Row (I guess at the end of the table) - End of the table, below the last project data
- Column (I suppose from the initial column of table) - Correct, Column A in this case

Hope this is clarifying the situation.
Thanks again!