Copying specific data from Sheet 1 to Sheet 2

  • Thread starter Thread starter Jock
  • Start date Start date
J

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?
 
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
 
Back
Top