Using VBA to Copy a Row to Another Worksheet

[jar8j]

I am creating a workbook with a large table. I would like the user be able to select certain rows and move them to another table for better comparison. Here is what I want to happen:

1. The user shall be able to specify and select a row from the table in Sheet 1. I have done this so far by the user clicking on the first cell of the row, and the system selects the entire row. I am open to other more user-friendly methods of selecting the row, like radio buttons or check boxes to the left of each row in the table.

2. The system shall copy that row and paste it into the last row of a table in Sheet 2.

The VBA code I have thus far gives me the error "Run-time error '-2147417848 (80010108)': Method 'Select' of object 'Range' failed. The code is below:

Sub Populate()
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveCell.Select
Range(Selection, Selection.End(xlToRight)).Select
Sheets("new").Range("A1").Select
ActiveSheet.Paste
End Sub


Any help would be greatly appreciated! Thanks in advance

 

[SkipVought]

Hi,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Range(Target, Target.End(xlToRight)).Copy
  with Sheets("new")
     .Cells(.Cells(1,1).currentregion.rows.count, 1).pastespecial xlpasteall
  end with
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jar8j]

Hi SkipVought,

Thank you so much for responding to my post. Unfortunately when I pasted your code into my document and ran it, nothing happened. While there was no error, when I clicked on the cell, nothing happened. Do I need to add something else to the code you included?

Thanks

 

[SkipVought]

Hmmmm?

Works PERFECTLY.

Did you paste into the sheet object code window of the sheet other than new?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jar8j]

Restarted the computer and it works! Thanks again for your help. How do I get the copied cells to paste to the bottom or top of previously copied rows on sheet "new" instead of replacing them?

Jessica

 

[SkipVought]

sorry, I missed the +1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Range(Target, Target.End(xlToRight)).Copy
  with Sheets("new")
     .Cells(.Cells(1,1).currentregion.rows.count[b] + 1[/b], 1).pastespecial xlpasteall
  end with
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jar8j]

Thanks! This is great. My team really appreciates it.

Is there a way to specify that this code only works when we click in a specific column, let's say Column B, as opposed to any cell in the worksheet?

Thanks

 

[SkipVought]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  [b]
  if target.column <> 2 then exit sub
[/b]
  Range(Target, Target.End(xlToRight)).Copy
  with Sheets("new")
     .Cells(.Cells(1,1).currentregion.rows.count + 1, 1).pastespecial xlpasteall
  end with
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}