VBA code to copy data from specific columns in target row instead of entire row

[handoverhammer]

Hi Excel Wizards,

Right now once a drop down value is selected (ans), a time stamp is added to H and the entire row is copied to another sheet (ans) corresponding with the drop down. Here is a shorten version of what I have, with one example using the value TEST.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)    If Intersect(Target, Range("J:J")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim LastRow As Long
    On Error GoTo errhandler
    Dim ans As String
    ans = Target.Value


    If ans = "TEST" Then
    Cells(Target.Row, "H").Value = Now
    Target.EntireRow.Copy Sheets(ans).Cells(Sheets(ans).Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If

Sheets(ans).Select
    Target.EntireRow.Delete
    
errhandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

[INDENT][/INDENT]

Instead of copying data from the entire row, I would like to only copy data in that row from columns A:H.

So, what do I modify in this line of code?

Target.EntireRow.Copy Sheets(ans).Cells(Sheets(ans).Rows.Count, "A").End(xlUp).Offset(1, 0)

Thanks in advance!

 

[Joe4]

Try:

Range(Cells(Target.Row,"A"),Cells(Target.Row,"H")).Copy Sheets(ans).Cells(Sheets(ans).Rows.Count, "A").End(xlUp).Offset(1, 0)

 

[handoverhammer]

That works! Much appreciated Joe.

 

[Joe4]

You are welcome!