How to write Excel macro to copy selected row of data between two sheets?

15,322

Under Developer -> Visual Basic, put the following in a new module:

Sub CopySelection()
  Dim xlSel As Excel.Range
  Set xlSel = Excel.Application.Selection

  xlSel.Copy Excel.Application.Sheets("Sheet2").Range("A1")
End Sub

Then, under Developer -> Macros, select the sub, click Options, and assign a key.

Share:
15,322

Related videos on Youtube

Escape.
Author by

Escape.

Updated on September 18, 2022

Comments

  • Escape.
    Escape. about 1 year

    I'm not really experienced in the whole VBA area, i get how macros work but this is just out of my reach. Basically as i stated in the title i need to write a macro that will copy the selected row or cells from one sheet to another.

    Eg. First sheet contains a table with some data. User selects the row or desired cells, runs the macro via key shortcut, macro copies selected data to Sheet 2 and pastes it in the desired row(let's make it row 22). Also, if row 22(A:22) has some data in it, paste data in the next row(23).

    I hope i made it clear enough.

    I appreciate any hint or help i can get.

    • Ƭᴇcʜιᴇ007
      Ƭᴇcʜιᴇ007 over 9 years
      What have you got so far? Where are you getting stuck?
    • Rik
      Rik over 9 years
      Just a thought but did you try the macro recorder. You can start recording and do these steps. After that you stop the recording and you can save it as a macro. After that you can edit this macro and see how these commands are recorded. Perhaps you need to tweak the commands but you get a good idea of how VBA works.
  • Escape.
    Escape. over 9 years
    Got it, works like a charm. Thanks! Now i was wondering. If the desired row is full, can i make it paste to the next one? I went thru some of the answers here on SU and i think something along these lines could work(but it doesn't) Sub CopySelection() Dim xlSel As Excel.Range Set xlSel = Excel.Application.Selection Set Rng = Sheet2.Range("A1") If IsEmpty(Rng) Then xlSel.Copy Excel.Aplication.Sheets("Sheet2").Range("A1") Else xlSel.Copy Excel.Aplication.Sheets("Sheet2").Range("A2") End If End Sub
  • DarkMoon
    DarkMoon over 9 years
    Well, if you just want to add it to the next available row after all of the existing rows, you could use xlSel.Copy Excel.Application.Cells(Excel.Application.Cells.SpecialCells‌​(xlCellTypeLastCell)‌​.Row + 1, 1).
  • Escape.
    Escape. over 9 years
    I'm sorry, i don't think you understood me right. Sorry for being a PITA, what i'm trying to do is the following. Add the ability to macro you posted, to check IF A:22 is empty. If true then paste it, else move to A:23 and paste there. In case A:23 is full..you get it where i'm going with this..
  • DarkMoon
    DarkMoon over 9 years
    That was meant to replace the copy in the original answer. Instead of just copying to A1, it'll copy it to the cell that is 1 row under the last cell with data in it, in column 1. If that won't work because the empty cell might be in the middle somewhere, then you could something like: Dim row as Integer row = 22 ' this is the starting row to check for empty cells Do While Excel.Application.Cells(row, 1).Value <> "" row = row + 1 Loop xlSel.Copy Excel.Application.Sheets("Sheet2").Cells(row, 1) I'm not in front of a Windows PC right now but that should work.