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.
Related videos on Youtube
Author by
Escape.
Updated on September 18, 2022Comments
-
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 over 9 yearsWhat have you got so far? Where are you getting stuck?
-
Rik over 9 yearsJust 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. over 9 yearsGot 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 over 9 yearsWell, 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. over 9 yearsI'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 over 9 yearsThat 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.