How to specify the column range to be copied when moving data between sheets?

[AshleyB00]

Currently, I have Table 1 on Sheet 1, that uses Columns A to I, and another table a few columns over that are unrelated. I'm trying to copy a row from Table 1 to Sheet 2 when certain criteria is met, but only including columns A-I of Table 1. My current code successfully copies the row from Sheet 1 to Sheet 2, but includes all columns past Table 1. The code is also made to clear the contents from Sheet 1 after copied.
How do I add limit the range of columns to be copied, so it doesn't other affect tables in the same rows? I'm very new to this so please and thank you!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim Lastrow As Long

If Target.Column = 9 Then
Application.EnableEvents = False

r = Target.Row
Lastrow = Sheets("Completed").Cells(Rows.Count, "I").End(xlUp).Row + 1

If Target.Value = "Done" Then
    Rows(r).Copy Sheets("Completed").Cells(Lastrow, 1)
    Rows(r).ClearContents
End If

End If
Application.EnableEvents = True


End Sub

 

[HaHoBe]

Hi AshleyB00,

what about

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim Lastrow As Long

If Target.Column = 9 Then
  Application.EnableEvents = False
  
  r = Target.Row
  Lastrow = Sheets("Completed").Cells(Rows.Count, "I").End(xlUp).Row + 1
  
  If Target.Value = "Done" Then
    With Range("A" & r).Resize(1, 9)
      .Copy Sheets("Completed").Cells(Lastrow, 1)
      .ClearContents
    End With
  End If

End If
Application.EnableEvents = True

End Sub

Ciao,
Holger

 

[AshleyB00]

Hi AshleyB00,

what about

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim Lastrow As Long

If Target.Column = 9 Then
  Application.EnableEvents = False
 
  r = Target.Row
  Lastrow = Sheets("Completed").Cells(Rows.Count, "I").End(xlUp).Row + 1
 
  If Target.Value = "Done" Then
    With Range("A" & r).Resize(1, 9)
      .Copy Sheets("Completed").Cells(Lastrow, 1)
      .ClearContents
    End With
  End If

End If
Application.EnableEvents = True

End Sub

Ciao,
Holger

Amazing! It works perfectly. Thanks so much Holger!