Copying specific data from Sheet 1 to Sheet 2

[Jock]

In Sheet 1, when "CH" is selected from a drop down list in column M, I'd like
Excel to copy data from cells B-L from the same line and paste the values
(not formats or formulae) into the next available row in Sheet 2.
Can this be done?

 

[Per Jessen]

Hi

Sure, it can be done.

Its' an event code, so it has to go into the codesheet for sheet1. I Assume
you have headings in row 1. Change the CopyToCol as desired.

Private Sub Worksheet_Change(ByVal Target As Range)
CopyToCol = "A"
Set isect = Intersect(Target, Columns("M"))
If Not isect Is Nothing Then
If Target.Value = "CH" Then
Range("B" & Target.Row, Cells(Target.Row, "L")).Copy
TargetRow = Worksheets("Sheet2").Range(CopyToCol & "1"). _
End(xlDown).Offset(1, 0).Row
Sheets("Sheet2").Range(CopyToCol & TargetRow). _
PasteSpecial Paste:=xlPasteValues
End If
End If
End Sub

Regards,
Per