If I change drop down value to "Completed" in a table with header "Status"(This should run for entire column), but I am only able to do for a cell

Question

I am trying to write macros code where, when the value of drop down under a column in table with header "Status", changes to "completed", then Sub Completedarc should run automatically. I am able to write a code when action status is changed to completed in one column but not the entire column in a table. Also,Sub Completedarc() is only cut pasting values in another sheet but not deleting the overcall row and it is left blank

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = Range "Open_Project_Details[[#Headers],[Status]]") Then
   Select Case Target.Value
      Case "Completed"
        Call Completedarc
   End Select
 End If
End Sub

---

Sub Completedarc()
Rows(ActiveCell.Row).EntireRow.Cut
Sheets("Completed Archive").Select
Range("Completed_Archive[[#Headers],[Stack Rank]]").Select
Selection.End(xlDown).Select
If ActiveCell = "" Then
   ActiveSheet.Paste
Else
   ActiveCell.Offset(1).Activate
   ActiveSheet.Paste
End If
End Sub

Answer 1

If I well understood your question, this changed event will do what you need:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Target.ListObject Is Nothing Then
    If Intersect(Target.ListObject.HeaderRowRange, _
            Target.EntireColumn).value = "Status" Then
        If Target.value = "Completed" Then
             Call Completedarc(Target) 'added an argument...
        End If
    End If
 End If
End Sub

About the Completedarc sub, I do not understand what is to be done. Does your "Open_Project_Details" table starts from column A:A and you want copying the table Target row in the first empty cell of the "Completed_Archive" table/column "Stack Rank"? Do you want to copy it inserting a new row after the table last one?

If this last one supposition is what you want, please use the next code:

Sub Completedarc(Target As Range)
  Dim TRows As Long, shCA As Worksheet
  
  Set shCA = Worksheets("Completed Archive")
  TRows = shCA.Range("Completed_Archive[Stack Rank]").cells.count 
  If TRows = 1 Then TRows = TRows + 1
  Intersect(Target.ListObject.DataBodyRange, Target.EntireRow).Copy _
                 shCA.Range("Completed_Archive[Stack Rank]").cells(TRows)
  'the next code line only selects the row to be deleted. If it selects what you need
  'you would only replace `Select` with `Delete` and the code will delete such rows
  Target.EntireRow.Select 'Delete
End Sub

Answer 2

Cut/Paste Table Row on Cell Change

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    completeArchive Target
End Sub

Sub completeArchive(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 Then
        Dim ws As Worksheet: Set ws = Target.Worksheet
        Dim rg As Range
        Set rg = Intersect(Target, ws.Range("Open_Project_Details[Status]"))
        If Not rg Is Nothing Then
            If rg.Value = "Completed" Then
                Set rg = Intersect(ws.Rows(rg.Row), _
                    ws.Range("Open_Project_Details"))
                With ws.Parent.Worksheets("Completed Archive")
                    With .Range("Completed_Archive[Stack Rank]")
                        rg.Copy .Cells(.Rows.Count + 1)
                        rg.Delete
                    End With
                End With
            End If
        End If
    End If
End Sub