-2

I would like to copy selected row from Sheet1 to Sheet2. Below code is overriding existing value in Sheet2 file. Please assist to stop override my existing values.

Sub CopySelection()

Dim xlSel As Excel.Range

Set xlSel = Excel.Application.Selection  
xlSel.Copy Excel.Application.Sheets("All_Data").Range("A1")

End Sub

Any help will be appreciated.

Community
  • 1
  • 1
RKVALU
  • 25
  • 5

1 Answers1

1

The code below will copy the range selected in "Sheet1" (modify it to fit your sheet's name that holds the data to be copied), and then will paste it in Column A in the first available row in Sheet "All_Data".

Let me know if the code below (tested) works :

Sub CopySelection()

Dim Sht1                As Worksheet
Dim Sht2                As Worksheet
Dim xlSel               As Range
Dim LastRow             As Long

' modify "Sheet1" to your sheet source (where you make your Selection to copy)
Set Sht1 = ThisWorkbook.Sheets("Sheet1")

' sheet "All_Data" is your target sheet (Where you paste the selection)
Set Sht2 = ThisWorkbook.Sheets("All_Data")

Set xlSel = Selection     

'option 1: find last row with data in Sheet "All_Data" at Column A
LastRow = Sht2.Cells(Sht2.Rows.Count, "A").End(xlUp).Row

'option 2: (less reliable) find last row with data in Sheet "All_Data" using UsedRange
'LastRow = Sht2.UsedRange.Rows(Sht2.UsedRange.Rows.Count).Row

' paste the copied range in Column A, the first row after row with data
xlSel.Copy Sht2.Range("A" & LastRow + 1)

End Sub
Shai Rado
  • 33,032
  • 6
  • 29
  • 51
  • UsedRange is not reliable: http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba – David Zemens Oct 06 '16 at 17:36
  • @DavidZemens I agree, but the PO didn't specify the structure of his data, where does the last row with data is located (which Column). Also , the entire concept here (using `Selection`) is not that reliable. Im just trying to figure out what does the PO mean – Shai Rado Oct 06 '16 at 17:38
  • I disagree, it's usually OK to use `Selection` as a way of gathering user input. The alternative is to code an `InputBox` object that asks for a selection, anyways. Further, you can always feel free to make additional improvements in the OP code when giving an answer :) – David Zemens Oct 06 '16 at 17:39
  • @DavidZemens ok, added the more reliable way finding the last row , per your recommendation :) – Shai Rado Oct 06 '16 at 17:44
  • thank you Shai, It really worked. One more small help I would like to add a small timer in cell sheet1. My need is as soon as my report is refreshed and if there is a new value added the timer should automatically start. Is it possible? – RKVALU Oct 07 '16 at 06:30
  • @RKVALU it is possible (I think), I have not used Timers for a long time. You should post a new post, with the edited code, and your new challenge – Shai Rado Oct 07 '16 at 06:35
  • how to paste the values without formula's ? – RKVALU Oct 13 '16 at 04:26