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]

IMPORTANT: Put the following code in the events of the "Project Status" sheet.

SHEET EVENT
Right click the tab of the "Project Status" sheet, select view code and paste the code into the window that opens up.

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("Projects in progress").ListObjects("Projects")
      tbl.ListRows.Add , 1
      i = tbl.ListRows.Count
      tbl.DataBodyRange(i, 1).Resize(1, 22).Value = Range("A" & Target.Row).Resize(1, 22).Value
      Application.EnableEvents = False
      Target.EntireRow.Delete
      Application.EnableEvents = True
    End If
  End If
End Sub

 

[wedloski]

That's perfect! It is working now. Thank you so much for help and patience. I really appreciate this.

IMPORTANT: Put the following code in the events of the "Project Status" sheet.

SHEET EVENT
Right click the tab of the "Project Status" sheet, select view code and paste the code into the window that opens up.

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("Projects in progress").ListObjects("Projects")
      tbl.ListRows.Add , 1
      i = tbl.ListRows.Count
      tbl.DataBodyRange(i, 1).Resize(1, 22).Value = Range("A" & Target.Row).Resize(1, 22).Value
      Application.EnableEvents = False
      Target.EntireRow.Delete
      Application.EnableEvents = True
    End If
  End If
End Sub

 

[wedloski]

DanteAmor,
I just have discovered one important fact, everything works perfectly fine, but it's deleting entire row, where further there is data used for drop-down lists. Would it be possible to delete only the columns that are in the table? Meaning columns from A to W.

IMPORTANT: Put the following code in the events of the "Project Status" sheet.

SHEET EVENT
Right click the tab of the "Project Status" sheet, select view code and paste the code into the window that opens up.

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("Projects in progress").ListObjects("Projects")
      tbl.ListRows.Add , 1
      i = tbl.ListRows.Count
      tbl.DataBodyRange(i, 1).Resize(1, 22).Value = Range("A" & Target.Row).Resize(1, 22).Value
      Application.EnableEvents = False
      Target.EntireRow.Delete
      Application.EnableEvents = True
    End If
  End If
End Sub

 

[DanteAmor]

Try this

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("Projects in progress").ListObjects("Projects")
      tbl.ListRows.Add , 1
      i = tbl.ListRows.Count
      tbl.DataBodyRange(i, 1).Resize(1, 22).Value = Range("A" & Target.Row).Resize(1, 22).Value
      Application.EnableEvents = False
      Range("A" & Target.Row).Resize(1, 22).Delete xlUp
      Application.EnableEvents = True
    End If
  End If
End Sub

 

[wedloski]

Thank you very much! Now everything is working how I wanted to.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[wedloski]

I'm glad to help you. Thanks for the feedback.

Here I come with another question. So basically right now in order to move the row, the condition is to have "accepted" in the cell. Things are getting a little bit more complicated, because there is one more column "I" in this scenario that has to meet the condition together with column "W". So the row can be moved only if in column "I" the value says "finished" and in column "W" the value says "accepted". As far as my research went the command needed is "if and", where both of the values in different columns have to meet their requirements in order to complete the macro and be moved from one table to another. So the condition to move the row has changed.

Name of the Origin Data:
- Sheet - "Project Status"
- Table - "Status"
- Columns - Column I -(value "finished") and Column W - (value "accepted"), so only if these 2 values are in place the row can be moved
- 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

And a question out of curiosity, can you add multiple conditions in a single macro, and only when a specific condition will be met then action will be taken to move let's say a row to different locations(I mean if a row will meet the condition that i set in the macro would be moved to a specific target, but when the condition change the exact same row will be moved to another location)? Or is it complex and its better to separate the commands?

Thanks for helping out again!