copy current row and paste to first empty row on sheet2

[wisewood]

Can someone please help me with a script that will detect the row that the current cell is in, select all cells in that row, copy them, and paste them into the first empty row on Sheet2.

A colleague of mine is wasting loads of time doing this manually over and over again.

 

[Lewiy]

Give this a try:

Sub MyMacro()
Dim limit As Long
limit = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1
Rows(ActiveCell.Row).Copy Destination:=Sheets("Sheet2").Rows(limit)
End Sub

 

[wisewood]

Runtime error '9':

Subscript out of range




Have i got the code in the wrong place? Where should it be?

 

[Lewiy]

Is the worksheet you are copying to called “Sheet2”? If not then you will need to replace the word Sheet2 in the code with the name of your worksheet.

 

[wisewood]

Thanks. I got there in the end.

Now I'm off to see if i can add in a few extra bits like highlighting the row background in grey... and maybe trigger it automatically.

Thanks.