Copy Selected Rows To Another Sheet

bjwade62 · Nov 29th 2006

I need to copy only user selected rows (multiple) from one sheet to another. I see similar threads here but none that work with user selected rows. Can anyone help me?

Thanks,
Bernie

Anonymous · Nov 29th 2006

Re: Copy Selected Rows To Another Sheet

If the user has already selected the rows, then this would paste the selected rows to the range starting with the first empty cell in column A of Sheet2.

Code

Selection.Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

bjwade62 · Nov 29th 2006

Re: Copy Selected Rows To Another Sheet

I ended up doing something similar. Thanks.

Quote from thomach
If the user has already selected the rows, then this would paste the selected rows to the range starting with the first empty cell in column A of Sheet2.
Code
Selection.Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

Dave Hawley · Nov 30th 2006

Re: Copy Selected Rows To Another Sheet

Here is another way, which allows for non-contiguous rows

Code

Sub CopySelectedRows()
  Dim rCopy As Range
  Dim lAreas As Long
  
  On Error Resume Next
  Set rCopy = Application.InputBox("Select Rows to copy. " _
  & "Use Ctrl for non-contiguous rows", "COPY ROWS", Selection.Address, , , , , 8)
  On Error GoTo 0
  If rCopy Is Nothing Then Exit Sub 'Hit Cancel
  
  If rCopy.Columns.Count <> Columns.Count Then
     MsgBox "Please select entire row(s)"
     Run "CopySelectedRows"
  Else
     For lAreas = 1 To rCopy.Areas.Count
        rCopy.Areas(lAreas).Copy Sheet2.Cells(Rows.Count, 1).End(xlUp)(2, 1)
     Next lAreas
  End If
  
End Sub

Display More

bjwade62 · Nov 30th 2006

Re: Copy Selected Rows To Another Sheet

Thanks Dave.

Quote from Dave Hawley

Here is another way, which allows for non-contiguous rows

Code

Sub CopySelectedRows()
  Dim rCopy As Range
  Dim lAreas As Long
  
  On Error Resume Next
  Set rCopy = Application.InputBox("Select Rows to copy. " _
  & "Use Ctrl for non-contiguous rows", "COPY ROWS", Selection.Address, , , , , 8)
  On Error GoTo 0
  If rCopy Is Nothing Then Exit Sub 'Hit Cancel
  
  If rCopy.Columns.Count <> Columns.Count Then
     MsgBox "Please select entire row(s)"
     Run "CopySelectedRows"
  Else
     For lAreas = 1 To rCopy.Areas.Count
        rCopy.Areas(lAreas).Copy Sheet2.Cells(Rows.Count, 1).End(xlUp)(2, 1)
     Next lAreas
  End If
  
End Sub

Display More

babar90 · May 13th 2022

Hi,

in this code, i would like the data to paste in Values Format .this code copy formulas on destination sheet. i want to copy only values not formula. Could you please help me on this.

Thanks in advance

Code

Sub CopySelectedRows()
Dim rCopy As Range
Dim lAreas As Long


On Error Resume Next
Set rCopy = Application.InputBox("Select Rows to copy. " _
& "Use Ctrl for non-contiguous rows", "COPY ROWS", Selection.Address, , , , , 
On Error GoTo 0
If rCopy Is Nothing Then Exit Sub 'Hit Cancel


If rCopy.Columns.Count <> Columns.Count Then
MsgBox "Please select entire row(s)"
Run "CopySelectedRows"
Else
For lAreas = 1 To rCopy.Areas.Count
rCopy.Areas(lAreas).Copy Sheet18.Cells(Rows.Count, 1).End(xlUp)(2, 1)


Next lAreas
End If


End Sub

Display More

**royUK** · May 14th 2022

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Just highlight all of the code and press the <> in the post menu above button to add the code tags.

Also, please start your own post. Posting in another member's Thread is known as hijacking and is not allowed here. By all means add a link to a Thread that may be related to your question.

Copy Selected Rows To Another Sheet

Participate now!

Share