title: Excel 2010: Need to copy certain cells to different worksheet based on cell value
body: <p>I have a spreadsheet in Excel 2010, that I am wanting to take certain cells in a row and copy them to another worksheet when a certain value is entered into a specific cell in the same row.</p>
  
  <p>For example: In Worksheet A, in cell A2, if a "X" is entered, I want cell A5, A7 and A13-17 to be copied to Worksheet B. In Worksheet B, they should be copied to the next available row, but I need them copied to specific cells (not the same as in Worksheet A- i.e: cell A5 would need to copy to cell 2 in Worksheet B; A7 to cell 4 and A13-17 to 10-14). And the cells they would be copied to in Worksheet B would also need to be created in a new row. </p>
  
  <p>I have some VBA that does something similar; however, it copies the entire row. For the above, I need only specific cells AND they won't match same cells in Worksheet B. </p>
  
  <p>Here is what I have been able to get to work (to copy entire row):</p>
  
  <pre><code>Sub Testing_Issue(ByVal Target As Range)
    'Disable events so code doesn't re-fire when row is deleted
      Application.EnableEvents = False
    'Was the change made to Column T?
      If Target.Column = 20 Then
    'If yes, does the Target read "Y"?
      If Target = "Y" Then
    'If Y, determine next empty row in Sheet "TEST"
      nxtRw = Sheets("TEST").Range("A" &amp; Rows.Count).End(xlUp).Row + 1
  
    'Copy the row from the Open worksheet and move to the TEST worksheet
      Target.EntireRow.Copy Destination:=Sheets("TEST").Range("A" &amp; nxtRw)
      Else
        MsgBox ("That is not a valid entry")
      End If
    End If
  'Re-enable Events
     Application.EnableEvents = True
   End Sub
  </code></pre>
  
  <p>I tried using the following instead of the entire row part, but does not work.</p>
  
  <pre><code>Target.Range("A13").Copy Destination:=Sheets("TEST").Range("A5 &amp; nxtRw")
  Target.Range("B13").Copy Destination:=Sheets("TEST").Range("C5 &amp; nxtRw")
  Target.Range("I13:J13").Copy Destination:=Sheets("TEST").Range("E5 &amp; nxtRw")
  </code></pre>
  
  <p>I would greatly appreciate some advice, as I really need to get this working right. Please let me know if something is not clear. </p>