title: Copying Cell Value to another sheet in Excel with VBA
body: <p>I have the following code which successfully copies cells B, E and F to cells B, C, D in sheet 2 after I match a string in another cell. The problem is that it copies the cell and not only just the value inside it (I don't need borders, colour etc).</p>
  
  <p>Another issue I have is that while it will copy the data to the next free row in Column B, it won't look for the next free row according to column C and D.</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  Dim thisrow As Long
  Dim lr As Long
  If Target.Column = 7 Then
      thisrow = Target.Row
  If Target.Value = "FAULTY" Then
      lr = Sheets("sheet2").Range("B" &amp; Rows.Count).End(xlUp).Row + 1
      Range("B" &amp; ActiveCell.Row).Copy Sheets("sheet2").Range("B" &amp; lr)
      Range("D" &amp; ActiveCell.Row).Copy Sheets("sheet2").Range("C" &amp; lr)
      Range("F" &amp; ActiveCell.Row).Copy Sheets("sheet2").Range("D" &amp; lr)
  End If
  End If
  
  End Sub
  </code></pre>