How to copy entire row from worksheet to another worksheet

Question

I'm relatively new to VBA. I have this sub procedure CutePaste, that I call in worksheet_change(ByVal Target As Range), that executes whenever the value in column "F" is changed. My goal is to copy the entire row of the cell changed and paste it into another sheet ("Cast Worked"). My code right now only copies the updated cell and paste that to the new sheet. Please advise how I can copy the entire row of the updated cell.

Sub CutPaste()

  If Not Intersect(myTarget, Range("F:F")) Is Nothing Then
    ActiveCell.Activate    
    a = Sheets("Cast Worked").Cells(Rows.Count, "A").End(xlUp).Row + 1    
    Sheets("Cast Worked").Range("A" & a).Value = ActiveCell.Value    
    ActiveCell.Offset(1, 0).Select    
  End If

End Sub

JNevill JNevill · Accepted Answer · 2020-04-30T13:43:20

You are close. You can use the Range.Copy method to do this. Furthermore you need to pass the target from the worksheet_change event to your subroutine.

Sub worksheet_change(ByVal Target As Range)
  'Pass target range to subroutine
  CutPaste(Target)
End Sub

Sub CutPaste(myTarget As Range) 
  If Not Intersect(myTarget, Range("F:F")) Is Nothing Then        
    a = Sheets("Cast Worked").Cells(Rows.Count, "A").End(xlUp).Row + 1    
    Target.EntireRow.Copy Destination:=Sheets("Cast Worked").Range("A" & a)
    Target.Offset(1, 0).Select    
  End If  
End Sub

I removed ActiveCell.Activate since setting the active cell to active is superfluous.

How to copy entire row from worksheet to another worksheet

1 Answers