Copy row to first empty row in new sheet

[Taylor]

I would like to:

1. Copy a row of data
2. Paste it into the first empty row on another sheet
3. Return to the sheet where the data was originally entered
4. Clear the information previously entered in the aforementioned row

Forgive me, but I know little to nothing about VBA language, so an
easy-to-understand response is greatly appreciated!

Thanks in advance!

 

[Mike H]

Hi,

You don't say which row you want to copy so this copies row 1.

Alt + F11 to open VB editor
Doubleclick 'This workbook' and paste this in on the right

Sub copyit()
Sheets("Sheet1").Rows(1).Copy
lastrow = Sheets("Sheet2").Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
Sheets("Sheet2").Range("A" & lastrow).PasteSpecial
Sheets("Sheet1").Rows(1).ClearContents
End Sub

Mike

 

[Gord Dibben]

Sub findbottom_paste22()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ActiveCell.EntireRow
Set rng2 = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
With rng1
.Copy Destination:=rng2
.EntireRow.Delete
End With
End Sub


Gord Dibben MS Excel MVP

 

[Rick Rothstein \(MVP - VB\)]

You didn't say what row you are copying, so I am assuming it is the row you
are on (the one with the active cell on the active worksheet) when you
activate the macro. You do, however, have to change my worksheet reference
from Sheet8 which I used in my code to whatever the actual worksheet name is
(make sure it is wrapped in quote marks like I show in my example) where you
are copying the information to.

Sub MoveCurrentRow()
With ActiveCell.EntireRow
.Copy Worksheets("Sheet8").Cells(Rows.Count, "A").End(xlUp).Offset(1)
.Clear
End With
End Sub

Also note that all I do is "clear" the data from the current row because you
said "clear the information". If instead of just erasing the data, you
actually wanted to delete the entire row (so that you wouldn't have blank
rows remaining on your current sheet where you activated the macro from),
then use this code instead...

Sub MoveCurrentRow()
With ActiveCell.EntireRow
.Copy Worksheets("Sheet8").Cells(Rows.Count, "A").End(xlUp).Offset(1)
.Delete
End With
End Sub

Rick